How to find available weekend between dates

 

How to find available weekend between dates so that the DBA can schedule database maintenance activity.

 

DECLARE @beginDate DATE = '20230301'

DECLARE @endDate DATE = '20230331' 

 

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