Script to find a list of Weekends between two Dates in SQL Server

This below query to get all weekends between the given date range.

DECLARE @beginDate DATE = '20201201'

DECLARE @endDate DATE = '20201231'  

 

DECLARE @Weekend TABLE

(

 Weekend DATE PRIMARY KEY

,IsWeekend BIT

)

 

WHILE @beginDate <= @endDate 

BEGIN 

INSERT INTO @Weekend 

SELECT @beginDate AS Weekend 

,(CASE WHEN DATEPART(WEEKDAY, @beginDate) In (7, 1) THEN 1 ELSE 0 END) AS IsWeekend 

SET @beginDate = DateAdd(Day, 1, @beginDate) 

END

 

SELECT Weekend FROM @Weekend WHERE IsWeekend = 1