Get only Saturday and Sunday dates of month in SQL Server

If you want to generate the data, then a CTE is the way to go

DECLARE

     @MONTH INT

    ,@YEAR INT

;

SET @MONTH = 6;

SET @YEAR = 2020;

WITH CTE AS

(

    SELECT CAST(CAST(@MONTH AS VARCHAR(2)) + '/01/' + CAST(@YEAR AS VARCHAR(4)) AS [Date]) AS DATE

        UNION ALL

    SELECT DATEADD(DAY,1,[Date])

    FROM CTE

    WHERE DATE <= CAST(@MONTH AS VARCHAR(2)) + 

        CASE 

            WHEN @MONTH IN (9,4,6,11) 

                THEN '/30/'

            WHEN @MONTH IN (1,3,5,7,8,10,12) 

                THEN '/31/'

            WHEN @MONTH = 2 AND @YEAR/4.00 = @YEAR/4 

                THEN '/29/'

            ELSE '/28/'

        END

        + CAST(@YEAR AS VARCHAR(4))

)

SELECT 

     [Date]

    ,CASE DATEPART(dw,[Date])

        WHEN 1 THEN 'Sunday'

        WHEN 2 THEN 'Monday'

        WHEN 3 THEN 'Tuesday'

        WHEN 4 THEN 'Wednesday'

        WHEN 5 THEN 'Thursday'

        WHEN 6 THEN 'Friday'

        WHEN 7 THEN 'Saturday'

    END

FROM CTE

WHERE DATEPART(dw,[Date]) IN (1,7)

OPTION (MAXRECURSION 0)

;

Output :