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

Your current issue is that you can't call a stored procedure from a select. You need a function for that. Here is a working function.

Another issue - avoid making recursive sql to count the sundays in a month. This "simple" expression will calculate the sundays in a month.

CREATE function f_count_sundays
(
  @Year int,
  @Month int
) returns int
BEGIN
DECLARE @x date = dateadd(month,@month-1+(@year-1900)*12,6)
RETURN datediff(d, dateadd(d,datediff(d,-1,@x)/7*7,-1),dateadd(m,1,@x))/7
END

go

SELECT dbo.f_count_sundays(2016,10)

Result:

5

Answer 2

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)