How can I count how many Sundays is in one month in SQL Server?

Question

Here is just one Sunday, but I can't count how many Sundays are in one month :

select dateadd(dd, 8 - datepart(dw, '2019-11-01'), '2019-11-01')

Answer 1

I have Updated the Code to more flexible

Declare @Month Bigint, @Year Bigint, @DayName VARCHAR(10) = 'Thursday'
Declare @date date
SET @Year = 2024
SET @Month = 2
SET @date = CAST(@Year AS VARCHAR(4)) + '-' + (CASE WHEN @Month > 9 THEN CAST(@Month AS VARCHAR(2)) ELSE '0' + CAST(@Month AS VARCHAR(2)) END)  +'-01'
Declare @CountDays int = 0
while DATEPART(Month,@date) = @Month
Begin
    --Select DATEPART(DAY,@date);
    --Select DATEPART( WEEKDAY, @DATE )
    DECLARE @Name VARCHAR(20)

    SELECT  @Name = CASE ( DATEPART(dw, @Date) + @@DATEFIRST ) % 7
                         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 0 THEN 'Saturday'
                       END 
    If @Name = @DayName
    Begin
        --Insert Data Into Your Table
        --Select @date
        SET @CountDays = @CountDays + 1
    End
    
    Set @date = DATEADD(Day, 1, @date); 
End

SELECT @CountDays