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.