SQL SERVER – Get Date of All Weekdays or Weekends of the Year

Today’s article is created based on wonderful contribution from Tejas Shah. Tejas is very prominent SQL Expert and .NET wizard. He has answered the query of a reader on this blog who raised the following question: how to generate the date for all the Sundays in the upcoming year. Tejas replied here with a script.

What I really liked about the script is that it is very easy to understand, and also it can be customized very quickly.

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
WHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday'-- For Sunday
*/
OPTION (MaxRecursion 370)

The result will be dates along with days in next column as expected.

SQL SERVER - Get Date of All Weekdays or Weekends of the Year calweekend

You are welcome to contribute any script, which you think can be helpful to others.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction
Next Post
SQL SERVER – Fix Error 1949, Level 16: Cannot create index on view. The function yields nondeterministic results

Related Posts

28 Comments. Leave new

  • Well done but we don’t need to use recursive
    Here’s my script with the same result
    —————————————————-
    DECLARE @FirstDateOfYear DATETIME
    SET @FirstDateOfYear = ‘2010-01-01’
    SELECT DISTINCT DATEADD(d, number, @FirstDateOfYear),
    CASE DATEPART(dw, DATEADD(d, number, @FirstDateOfYear))
    WHEN 7 THEN ‘Saturday’
    WHEN 1 THEN ‘Sunday’
    ELSE ‘Work Day’
    END
    FROM master..spt_values
    WHERE number BETWEEN 0 AND 364
    AND (DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 1 OR DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 7)
    ORDER BY DATEADD(d, number, @FirstDateOfYear)

    Reply
  • Good Script Tejas!!!.

    When talk comes to date script, I would also like to share one of the script to find out first and last Friday of the years. starting from Jan-06 to Dec-2020. Actually I have got this request from one of the forums and created this very quickly.

    WITH CTE AS
    (
    select CONVERT(datetime,’01/06/2006′) as dt, datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
    union all
    select s.dt+7 as dts,datename(dw,dt+7) as dy ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
    from cte s
    where s.dt<CONVERT(datetime,'12/31/2020')
    )
    select MAX(dt) as dt,dy,mn,yr from cte
    group by dy,mn,yr
    UNION
    select MIN(dt) as dt,dy,mn,yr from cte
    group by dy,mn,yr
    ORDER BY dt
    OPTION (MaxRecursion 0)

    Reply
  • Really very usefull scripts…!

    Reply
    • Hi, This is great script and will be helpful in multiple ocassions (espically when join with my time dimension), But i have one question though.

      Can some one explain me how
      OPTION (MAXRECURSION 370); works?, Also how do you arrive at this number – 370. I am trying to understand from BoL but still not able to get it. Any help in this regard with be great.

      Reply
      • Hi Shan,

        MAXRECURSION query hint in common table expression is used to recursively execute the last statement in CTE that is joined using UNION, UNION ALL, INTERSECT or EXCEPT.
        Here The number 370 is used to keep recursion under the number of days in a year (365).

        Regards,
        Pinal Dave

  • Dave Ballantyne
    December 29, 2009 7:15 pm

    Personally, i prefer to use a Calendar table for this sort of operation.

    Reply
  • Hi dba guy,
    Your query is very nice to understand .but i have one doubt is that you used master..spt_values just for getting numbers from 0 to 364 or there is any other specific reason.
    Thanks
    Rahul

    Reply
  • Pinal,

    It wont work in non-English servers
    So better usage is

    SELECT FromDate AS Date, Dayname
    FROM CTE
    WHERE datepart(weekday,Fromdate) in (7,1)

    Reply
  • hai

    can anybody give me the query for retriving previous year and month details.

    Reply
  • thankx

    it is very helpful to query

    i have another problem

    DECLARE @date DATETIME;
    SET @date = ‘2006-10-15’;
    Select datename(MONTH,DateAdd(mm,-12,@date)),datename(year,DateAdd(mm,-12,@date)) union all
    select datename(MONTH,DateAdd(mm,-11,@date)),datename(year,DateAdd(mm,-11,@date))

    union all
    select datename(MONTH,DateAdd(mm,-10,@date)),datename(year,DateAdd(mm,-10,@date)) union all
    select datename(MONTH,DateAdd(mm,-09,@date)),datename(year,DateAdd(mm,-09,@date)) union all
    select datename(MONTH,DateAdd(mm,-08,@date)),datename(year,DateAdd(mm,-08,@date)) union all
    select datename(MONTH,DateAdd(mm,-07,@date)),datename(year,DateAdd(mm,-07,@date)) union all
    select datename(MONTH,DateAdd(mm,-06,@date)),datename(year,DateAdd(mm,-06,@date)) union all
    select datename(MONTH,DateAdd(mm,-05,@date)),datename(year,DateAdd(mm,-05,@date)) union all
    select datename(MONTH,DateAdd(mm,-04,@date)),datename(year,DateAdd(mm,-04,@date)) union all
    select datename(MONTH,DateAdd(mm,-03,@date)),datename(year,DateAdd(mm,-03,@date)) union all
    select datename(MONTH,DateAdd(mm,-02,@date)),datename(year,DateAdd(mm,-02,@date)) union all
    select datename(MONTH,DateAdd(mm,-01,@date)),datename(year,DateAdd(mm,-01,@date))
    This is the query that retrieves data for past one year with month .
    But if we want to retrieve more than past 24 months and more its not easier to query.

    Is there any other solution for this comments

    Reply
    • CREATE PROCEDURE SP_MONTH
      @COUNTER INT
      AS
      BEGIN
      DECLARE @TEMPTABLE TABLE ([MONTH NAME] VARCHAR(50), [MONTH YEAR] VARCHAR(6))
      DECLARE @RESULT VARCHAR(MAX)
      WHILE @COUNTER > 0
      BEGIN
      INSERT INTO @TEMPTABLE VALUES (DATENAME(MONTH,DATEADD(MM, -@COUNTER, GETDATE())),DATEPART(YYYY, DATEADD(MM, -@COUNTER, GETDATE())))
      SET @COUNTER = @COUNTER – 1
      END
      SELECT * FROM @TEMPTABLE
      END

      EXEC SP_MONTH 24

      This procedure can calculate nos of month whatever u want

      Reply
  • DECLARE
    @weekDay TINYINT,@daySaturday DATETIME,@daySunday DATETIME

    SET
    @weekDay = DATEPART(dw,’2010′)

    SELECT
    @daySaturday = DATEADD(dd,7-@weekDay,’2010′),
    @daySunday = DATEADD(dd,7-@weekDay+1,’2010′)

    SELECT
    DATEADD(dd,(number-1)*7,@daySaturday) ‘SATURDAY’,
    DATEADD(dd,(number-1)*7,@daySunday) ‘SATURDAY’
    FROM
    dbo.tally
    WHERE
    number < = 53 AND
    DATEADD(dd,(number-1)*7,@daySunday) <= '01/01/2011'

    Reply
  • Hi frnds

    I have table named Holidaylist .
    with columns Hid,Hname,date,month,year.I want to retrieve
    secondworking day of the current month that includes
    saturday and sunday as holidays

    Provide me the solution

    thankx in advance

    Reply
  • Veru useful script!!

    Reply
  • hi,

    can anybody give me the query for retriving only monday of every month

    Reply
    • This Procedure will help to get Monday in a month

      CREATE PROCEDURE SP_GETMONDAYINAMONTH
      @FRISTDAYOFMONTH DATETIME
      AS
      BEGIN
      DECLARE @NOOFDAYS INT
      SET @NOOFDAYS = (select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@FRISTDAYOFMONTH) as varchar)+’-‘+cast(month(@FRISTDAYOFMONTH) as varchar)+’-01′ as datetime)))))
      SELECT DISTINCT DATEADD(d, NUMBER, @FRISTDAYOFMONTH) AS DATE ,
      CASE DATEPART(dw, DATEADD(d, NUMBER, @FRISTDAYOFMONTH))
      WHEN 2 THEN ‘MONDAY’
      ELSE ‘WORKING DAY’
      END AS [DAY OF WEAK]
      FROM MASTER..SPT_VALUES
      WHERE NUMBER BETWEEN 0 AND @NOOFDAYS AND (DATEPART(dw, DATEADD(d, NUMBER, @FRISTDAYOFMONTH)) = 2)
      ORDER BY DATEADD(d, number, @FRISTDAYOFMONTH)
      END

      EXEC SP_GETMONDAYINAMONTH ‘2010-04-01’

      When you execute this procedure you have to pass the frist day of month in which month you want to calculate the no of Monday

      Reply
  • Hello Kranthi,

    Change the last SELECT statement of query in article as below:

    SELECT FromDate AS Date, Dayname
    FROM CTE
    WHERE DayName IN (‘Monday’)

    Although this querywould return all mondays of current year but it can be modified to retreive records only for a month.

    Regards,
    Pinal Dave

    Reply
  • Would you have any idea how to get a list of dates of the start and end of each week in a variable-specified date range?

    DECLARE @RangeStartDate DATETIME,
    @RangeEndDate DATETIME;

    SET @RangeStartDate = ‘2008-03-01’;
    –SELECT @RangeStartDate;
    SET @RangeEndDate = ‘2008-05-01’;
    –SELECT @RangeEndDate;

    Reply
  • Can Any one help how to calculate moving average monthly wise.

    Charge ,Payment, Month, AverageCharge,AveragePayment
    300 100 1 300 100
    500 150 2 400 125
    400 200 3 300 116.6666667
    200 50 4 150 62.5
    800 300 5 200 70
    450 100 6 208.3333333 66.66666667
    600 150 7 150 35.71428571

    just like we do in excel sheet.

    Reply
  • nice

    Reply
  • P.V.Rajagopal
    June 3, 2015 10:58 am

    declare @dayname table
    (
    dates datetime,
    daynames varchar(15)
    )
    declare @dates datetime = ’01-01-2015′
    declare @a int = 1;
    while @a <= 365
    BEGIN
    insert into @dayname
    (
    dates,daynames
    )
    values (@dates,datename(dw,@dates))
    set @dates = @dates + 1
    SET @a = @a + 1
    END

    select * from @dayname where daynames in ('Saturday','Sunday')

    Reply
  • Hi

    I want to get same result without CTE .I need to implement in SQL server 2005 version.

    Thanks & Regards

    Reply
  • Hi
    Would you have any idea how to get all Mondays from the specific year and specific month with two variables @Year, @ Month

    Reply
  • How to insert these two column in result into an existing table I have in my Data base ? Please provide a solution for it too.

    Reply

Leave a Reply