Wednesday, October 20, 2010

List all the Weekends of the Current Year using SQL Server

DECLARE @StrtDate datetime
DECLARE @EndDate datetime
SELECT @StrtDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
;WITH CTE (weekends)AS(SELECT @StrtDateUNION ALLSELECT DATEADD(d,1,weekends)
FROM CTEWHERE weekends < @EndDate)
SELECT weekends from CTEWHERE DATENAME(dw,weekends)In ('Saturday', 'Sunday')

No comments:

Post a Comment