Wednesday, August 14, 2013

How to find all Saturday and Sunday of the Current Month in MS SQL

I have seen many people asking the question “How to show all Saturdays and Sundays of the current month” in forums, so I thought I will try which is the best and easiest solution for this requirement.

Here I am going to use common table expression (CTE) to show the week ends with the date of the month. Below is the actual query you can use to find all the weekends of the current month with date.

 
WITH CTE(DATE, DayOfTheMonth)
AS
(
SELECT DATEADD(DAY, -DAY(GETDATE()-1), GETDATE()), 1
UNION ALL
SELECT DATE+1, DayOfTheMonth+1 FROM CTE WHERE DayOfTheMonth < (SELECT DAY(DATEADD(DAY, -DAY(DATEADD(MM, 1, GETDATE())), DATEADD(MM, 1, GETDATE()))))
)
 
SELECT DATENAME(WEEKDAY, DATE) AS WeekDay_Name, DayOfTheMonth INTO #TempDateTable FROM CTE OPTION(MAXRECURSION 30)
 
SELECT * FROM #TempDateTable WHERE WeekDay_Name IN('Saturday', 'Sunday')
 
DROP table #TempDateTable
 
 

When you execute above query you will get the out put as

WeekDay_Name DayOfTheMonth
Saturday 3
Sunday 4
Saturday 10
Sunday 11
Saturday 17
Sunday 18
Saturday 24
Sunday 25
Saturday 31

Depends on the Month your DayOfTheMonth value will change. Since I am executing this in August 2013 it shows the records for that month.

image

No comments: