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

Tutorialsrack 04/06/2019 SQL

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

Here is the code to get only Saturdays and Sundays dates of a month in SQL Server using CTE.

DECLARE @month date = '2019-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

I hope this article will help you to understand how to get only Saturdays and Sundays dates of a month in SQL Server using CTE.

Share your valuable feedback and help us to improve. If you find anything incorrect, or you want to share more information about the topic discussed above. please post your comment at the bottom of this article. Thank you!

Related Posts