–Pass two dates and it how many sundays occures
CREATE FUNCTION [dbo].[fnc_SundayCount](@StartDate datetime,@EndDate datetime)
RETURNS SMALLINT
as
Begin
SET @StartDate=CONVERT(datetime,CONVERT(VARCHAR, @StartDate,101))
SET @EndDate=CONVERT(datetime,CONVERT(VARCHAR, @EndDate,101))
DECLARE @DayDiff SMALLINT
DECLARE @tempVar SMALLINT
DECLARE @TotalSunday SMALLINT
SET @TotalSunday=0
SET @DayDiff=DATEDIFF(DAY,@StartDate,@EndDate)
SET @tempVar=0
WHILE (@tempVar < @DayDiff)
BEGIN
IF(DATEPART(weekday,@StartDate)=1)
BEGIN
SET @TotalSunday =@TotalSunday+1
END
SET @StartDate=@StartDate+1
SET @tempvar = @tempvar+1
END
Return @TotalSunday
END
Advertisement
