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

Leave a Reply

Menu