how many sundays in month sql query

how many sundays in month sql query

Solution:
Use this function.
First Find out How many days in month.

Create Function Dbo.HowManyDays(@Month  Varchar(15),@year varchar(15)) Returns Int
As
Begin
Declare @Date   Varchar(50),
        @Days   TinyInt
                

Select  @Date = '' + @year + '-' + @Month + '-01', 
        @Days = DatePart(Day,DateAdd(Month,1,@Date) -1)
               
Return @Days
End

-- How many days in Month

select dbo.HowManyDays('Jul','2018');

Output:

Next Find out How many Sundays.

Create FUNCTION HowManySundayInMonth(@Mon varchar(15),@year Varchar(15)) RETURNS INTEGER
AS
BEGIN
Declare @Days   TinyInt,
        @Month  Varchar(15)
  Declare  @DaysCount as Integer

Select @Month =@Mon
Select  @Days = Master.Dbo.HowManyDays(@Month,@year)

;With Sundays
As
(
Select 1 [Days]
Union All
Select [Days] + 1 from Sundays where [Days] <31
)
Select @DaysCount=count(*) from 
(
select [Days] from Sundays Where [Days] <= @Days and DATENAME(weekday,'' + @year + '-' + @Month + '-' +  Cast([Days] as varchar)) = 'Sunday'
) as X

return @DaysCount
End

-- How many sunday in Month

select dbo.HowManySundayInMonth('Jul','2018')