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.

[code:sql]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)[/code]

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

[code:sql]select dbo.fun_getDateDiffExcludeWeekEnd(@startDate,@endDate) + 1[/code]

Otherwise you can write it like below syntax.

[code:sql]select dbo.fun_getDateDiffExcludeWeekEnd(dateadd(d,-1,@startDate),@endDate)[/code]

Function for get date different with exclude weekends.

[code:sql]CREATE FUNCTION [dbo].[fun_getDateDiffExcludeWeekEnd]
(
@start datetime,
@end datetime
)
RETURNS numeric
AS
BEGIN
DECLARE @WeedDay numeric = 0;
DECLARE @TotalDayCount numeric = 0;

SELECT @WeedDay = datepart(weekday,@end);

IF @WeedDay = 1
BEGIN
SELECT @end = DATEADD(d,1,@end);
END
IF @WeedDay = 7
BEGIN
SELECT @end = DATEADD(d,2,@end);
END
SELECT @TotalDayCount = (DateDiff(d, @start, @end) – ( DateDiff(ww, @start, @end) * 2));
RETURN @TotalDayCount
END[/code]