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.

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

23 thoughts on “SQL SERVER – Get Date of All Weekdays or Weekends of the Year

  1. 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)

  2. 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)

    • 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.

      • 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

  3. 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

  4. 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

    • 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

  5. 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'

  6. 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

    • 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

  7. 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

  8. 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;

  9. 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.

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s