get DATEDIFF excluding weekends using sql server

Question

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.

Answer 1

Example query below, here are some details on how I solved it.

Using DATEDIFF(WK, ...) will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.

So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...) to get the number of weekdays.

This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.

You may also want to consider whether or not the DATEDIFF should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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)

Answer 6

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.

Answer 7

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

Answer 8

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

Answer 9

Using @Derek Kromm answer (Current Marked Answer)

I have modified so it IS tolerant of any localisations that may be on the target server.

DECLARE @d1 DATETIME, @d2 DATETIME
SELECT @d1 = '10/01/2022',  @d2 = '10/28/2022'

SELECT (datediff(dd, @d1, @EndQuery)+1) - (datediff(wk, @d1, dateadd(dd,1,@d2)) * 2) 
  - CASE WHEN DateName(WEEKDAY, @d1) = 'Sunday' THEN 1 ELSE 0 END -- This includes for start date edge case
  + CASE WHEN DateName(WEEKDAY, @d2) = 'Saturday'  THEN 1 ELSE 0 END -- This includes for  end date edge case.

This is with the end date being innclusive.

Answer 10

/* 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