[sql-server-2005] get DATEDIFF excluding weekends using sql server

I am using this query to get time taken.

SELECT DATEDIFF(dd, ActualStartDate, ActualCompletionDate) AS TimeTaken
FROM TableName

Now I want to exclude weekends and only include Mon-Fri as days counted.

This question is related to sql-server-2005

The answer is


I just want to share the code I created that might help you.

DECLARE @MyCounter int = 0, @TempDate datetime, @EndDate datetime;
SET @TempDate = DATEADD(d,1,'2017-5-27')
SET @EndDate = '2017-6-3'

WHILE @TempDate <= @EndDate
    BEGIN
    IF DATENAME(DW,@TempDate) = 'Sunday' OR DATENAME(DW,@TempDate) = 'Saturday'
        SET @MyCounter = @MyCounter
    ELSE IF @TempDate not in ('2017-1-1', '2017-1-16', '2017-2-20', '2017-5-29', '2017-7-4', '2017-9-4', '2017-10-9', '2017-11-11', '2017-12-25')
        SET @MyCounter = @MyCounter + 1

    SET @TempDate = DATEADD(d,1,@TempDate)
    CONTINUE

END
PRINT @MyCounter
PRINT @TempDate

If you do have a holiday table, you can also use that so that you don't have to list all the holidays in the ELSE IF section of the code. You can also create a function for this code and use the function whenever you need it in your query.

I hope this might help too.


declare @d1 datetime, @d2 datetime
select @d1 = '4/19/2017',  @d2 = '5/7/2017'

DECLARE @Counter int = datediff(DAY,@d1 ,@d2 )

DECLARE @C int = 0
DECLARE @SUM int = 0





 WHILE  @Counter > 0
  begin
 SET @SUM = @SUM + IIF(DATENAME(dw, 

 DATEADD(day,@c,@d1))IN('Sunday','Monday','Tuesday','Wednesday','Thursday')
 ,1,0)



SET @Counter = @Counter - 1
set @c = @c +1
end

select @Sum

Using https://stackoverflow.com/a/1804095 and JeffFisher30's answer above (https://stackoverflow.com/a/14572370/6147425) and my own need to have fractional days, I wrote this:

DateDiff(second,Start_Time,End_Time)/86400.0
            -2*DateDiff(week, Start_Time, End_Time)
            -Case When (DatePart(weekday, Start_Time)+@@DateFirst)%7 = 1 Then 1 Else 0 End
            +Case When (DatePart(weekday, End_Time)+@@DateFirst)%7 = 1 Then 1 Else 0 End

If you hate CASE statements as much as I do, and want to be able to use the solution inline in your queries, just get the difference of days and subtract the count of weekend days and you'll have the desired result:

declare @d1 datetime, @d2 datetime,  @days int
select @d1 = '2018/10/01',  @d2 = '2018/11/01'

SET @days = DateDiff(dd, @d1, @d2) - DateDiff(ww, @d1, @d2)*2
print @days

(The only caveat--or at least point to keep in mind--is that this calculation is not inclusive of the last date, so you might need to add one day to the end date to achieve an inclusive result)


/* EXAMPLE: /MONDAY/ SET DATEFIRST 1 SELECT dbo.FUNC_GETDATEDIFFERENCE_WO_WEEKEND('2019-02-01','2019-02-12') */ CREATE FUNCTION FUNC_GETDATEDIFFERENCE_WO_WEEKEND ( @pdtmaLastLoanPayDate DATETIME, @pdtmaDisbursedDate DATETIME ) RETURNS BIGINT BEGIN

DECLARE
@mintDaysDifference     BIGINT

SET @mintDaysDifference = 0

WHILE CONVERT(NCHAR(10),@pdtmaLastLoanPayDate,121) <= CONVERT(NCHAR(10),@pdtmaDisbursedDate,121)
BEGIN
    IF DATEPART(WEEKDAY,@pdtmaLastLoanPayDate) NOT IN (6,7)
    BEGIN
        SET @mintDaysDifference = @mintDaysDifference + 1
    END

    SET @pdtmaLastLoanPayDate = DATEADD(DAY,1,@pdtmaLastLoanPayDate)
END

RETURN ISNULL(@mintDaysDifference,0)

END


BEGIN 
DECLARE @totaldays INT; 
DECLARE @weekenddays INT;

SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) 
SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2) + -- get the number of weekend days in between
                       CASE WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN 1 ELSE 0 END + -- if selection was Sunday, won't add to weekends
                       CASE WHEN DATEPART(WEEKDAY, @endDate) = 6 THEN 1 ELSE 0 END)  -- if selection was Saturday, won't add to weekends

Return (@totaldays - @weekenddays)

END

This is on SQL Server 2014


I found when i used this there was a problem when d1 fell on saturday. Below is what i used to correct this.

declare @d1 datetime, @d2 datetime
select @d1 = '11/19/2011' ,  @d2 = '11/28/2011'

select datediff(dd, @d1, @d2) +case when datepart(dw, @d1) = 7 then 1 else 0 end - (datediff(wk, @d1, @d2) * 2) -
 case when datepart(dw, @d1) = 1 then 1 else 0 end +
 case when datepart(dw, @d2) = 1 then 1 else 0 end

Use this function to calculate the number of business days excluding Saturday and Sunday. Also it will exclude start date and it will include end date.

-- Select [dbo].[GetBussinessDays]  ('02/18/2021', '03/06/2021') -- 11 days
CREATE or ALTER FUNCTION [dbo].[GetBussinessDays] ( 
    @StartDate DATETIME,
    @EndDate  DATETIME 
)
returns INT AS
BEGIN
  DECLARE @tempStartDate     DATETIME= @StartDate;
  DECLARE @tempEndDate       DATETIME = @EndDate;
  
  IF(@tempStartDate IS NULL
  OR
  @tempEndDate IS NULL)
  BEGIN
    RETURN NULL;
  END
  --To avoid negative values reverse the date if StartDate is grater than EndDate
  IF(@StartDate > @EndDate)
  BEGIN
    SET @StartDate = @tempEndDate;
    SET @EndDate = @tempStartDate;
  END

  DECLARE @Counter           INT = Datediff(day,@StartDate ,@EndDate);
  DECLARE @TempCounter       INT = 0;
  DECLARE @TotalBusinessDays INT = 0;

  WHILE @Counter >= 0
  BEGIN
    IF(@TempCounter > 0 OR @Counter = 1) -- To ignore first day's calculation
    Begin
        SET @TotalBusinessDays = @TotalBusinessDays + Iif(Datename(dw, Dateadd(day,@TempCounter,@StartDate)) IN('Monday',
                                                                                                         'Tuesday',
                                                                                                         'Wednesday',
                                                                                                         'Thursday',
                                                                                                         'Friday'),1,0)
    END

    SET @Counter = @Counter - 1
    SET @TempCounter = @TempCounter +1
  END
  RETURN @TotalBusinessDays;
END