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,
-- 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
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
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.
You are welcome to contribute any script, which you think can be helpful to others.
Reference: Pinal Dave (http://blog.SQLAuthority.com)