Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008

Recently I had to pull out the number of weekends in this quarter and list them for a report. Here’s how I did it

DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
SELECT @date2= DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1)
;WITH CTE (dt)
AS
(
SELECT @date1
UNION ALL
SELECT DATEADD(d,1,dt) FROM CTE
WHERE dt < @date2
)
SELECT dt from CTE 
WHERE DATENAME(dw,dt) In ('Saturday', 'Sunday')

OUTPUT

If you want to list all the weekends in a year, just replace the @date1 and @date2 variables with the appropriate dates as shown here:

SELECT @date1 = '1/1/2009'
SELECT @date2= '12/31/2009'