sqlsql-serverfunction

Get Sundays For a given month Date in a function SQL


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

Solution

  • 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)