Sometime back, I had posted to Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008. An anonymous user wrote back asking if there was a way to determine the first and last day of the current year and rewrite the same query. Well here it is:
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 @StrtDate
UNION ALL
SELECT DATEADD(d,1,weekends)
FROM CTE
WHERE weekends < @EndDate
)
SELECT weekends from CTE
WHERE DATENAME(dw,weekends)
In ('Saturday', 'Sunday')
OUTPUT
Hi Excellent article.
ReplyDelete