Bharat Patel     Feb,08 2017

Date Difference Excluding Weekend

Recently I need to find the date difference between two dates excluding weekends. So I have made sql server scalar function to get date difference excluding weekends. You need to pass only two arguments as start date and end date. Refer below example. I hope it may be helpful.

Declare @startDate date, @endDate date;Set @startDate = CONVERT(date,'03/11/2010');Set @endDate = CONVERT(date,'03/25/2010');select dbo.fun_getDateDiffExcludeWeekEnd(@startDate,@endDate)

Basically datediff function start count from next to @startDate so if you want to get count with @startdate you need add +1 in count.

select dbo.fun_getDateDiffExcludeWeekEnd(@startDate,@endDate) + 1

Otherwise you can write it like below syntax.

select dbo.fun_getDateDiffExcludeWeekEnd(dateadd(d,-1,@startDate),@endDate)

Function for get date different with exclude weekends.

CREATE FUNCTION [dbo].[fun_getDateDiffExcludeWeekEnd](@start datetime,@end datetime)RETURNS numericASBEGINDECLARE @WeedDay numeric = 0;DECLARE @TotalDayCount numeric = 0;SELECT @WeedDay = datepart(weekday,@end);IF @WeedDay = 1BEGINSELECT @end = DATEADD(d,1,@end);ENDIF @WeedDay = 7BEGINSELECT @end = DATEADD(d,2,@end);ENDSELECT @TotalDayCount = (DateDiff(d, @start, @end) - ( DateDiff(ww, @start, @end) * 2));RETURN @TotalDayCountEND