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