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.

@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 (
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
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
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)

SQL DateTime, SQL Function, SQL Scripts
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