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



image



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

2 comments:

Anonymous said...

Is there a way to rewrite this query by finding the first and last day of the year programmatically, rather than hard coding it?

Suprotim Agarwal said...

I have posted an answer to your question over here http://www.sqlservercurry.com/2010/05/list-all-weekends-of-current-year-using.html