sql server - Get Sundays For a given month Date in a function SQL

Question

I am facing a problem. I want to get no of sunday in a month using a function although i have achieved a similar result using a procedure but i need to call that function in my select query to return result for each select. My code for the procedure is given

    create proc [dbo].[getSundaysandSaturdays]
(
    @Year int=2016,
    @Month int=11,
    @fdays as int output
)
as
begin
    ;with dates as
    (
        select dateadd(month,@month-1,dateadd(year,@year-1900,0)) as StartDate
        union all
        select startdate + 1 from dates where month(startdate+1) = @Month
    )
    select @fdays=count(*) from dates where datediff(dd,0,startdate)%7 in (4)
    return @fdays
end

Answer 1

I have changed like below and also modified logic required to find sunday's by using datepart function. If you want to return saturday's count also then add it in where clause.

 create function [dbo].[getSundaysandSaturdays]
    (
        @Year int,
        @Month int

    ) 
    RETURNS int
    as 
    begin
    declare     @fdays int 
        ;with dates as
        (
            select dateadd(month,@month-1,dateadd(year,@year-1900,0)) as StartDate
            union all
            select startdate + 1 from dates where month(startdate+1) = @Month
        )
        select @fdays=count(*) from dates where datepart(dw,StartDate) =1
      return @fdays
      end

And Call function like below

  select  dbo.[getSundaysandSaturdays](2015,11)