Technical Article

Function Number of Week Days between Two Dates

,

This function will calculate the number of weekdays between two dates passed. This allows for accurate tracking of "business days" for metrics in many different views.

CREATE FUNCTION dbo.fnDayDiffExcludingWeekEnds
(@StartDate datetime,
@EndDate datetime)
RETURNS int
AS  
BEGIN 
declare @ActualDateDiff int
declare @NewStartDate datetime
declare @Difference int
declare @TempDifference int

set @ActualDateDiff = datediff(dd, @StartDate, @EndDate)

select @Difference =  
case @ActualDateDiff
when 0 then 0--Same Day (just calculate days)
when 1 then 
case when datepart(dw,@Startdate) in (1,7) or datepart(dw,@EndDate) in (1,7) then 0 else 1 end
when 2 then--Could be 1 weekend day
case when datepart(dw,@Startdate) in (1,7) or datepart(dw,@EndDate) in (1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days 
else
case when @ActualDateDiff < 7 then --Less than a week
case @ActualDateDiff + datepart(dw,@Startdate) - 1
when 8 then @ActualDateDiff - 2
when 7 then @ActualDateDiff - 1
else 
@ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end

if @ActualDateDiff > 7
begin
set @NewStartDate = dateadd(dd,7,@StartDate)
set @Difference = isnull(@Difference,0) + dbo.fnDayDiffExcludingWeekEnds(@NewStartDate,@EndDate)
end


return @Difference
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating