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'

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook