How to find only Saturday and Sunday dates of month in SQL Server

How to find only Saturday and Sunday dates of month in SQL Server

In this post, we will see how to find only Saturdays and Sundays dates of a month in SQL Server using CTE (Common Table Expression).

DECLARE @month date = '2020-01-01'

SET @month = dateadd(month, datediff(month, 0, @month), 0)

;WITH CTE as
(
  SELECT 0 x
  FROM (values(1),(1),(1),(1),(1),(1)) x(n)
),
CTE2 as
(
  SELECT
    top(datediff(d, @month,dateadd(month,1,@month)))
    cast(dateadd(d, row_number()over(order by(select 1))-1,@month) as date) cDate
  FROM CTE CROSS JOIN CTE C2
)
SELECT
  cDate as 'Day Date',
  datename(weekday, cDate) as 'Week Day'
FROM CTE2
WHERE
  datediff(d,0,cDate)%7 > 4

OUTPUT

2020-01-11 Saturday
2020-01-12 Sunday
2020-01-18 Saturday
2020-01-19 Sunday
2020-01-25 Saturday
2020-01-26 Sunday

2020-02-18