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

User · Answer

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.

User · Answer

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   lt   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

User · Answer

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

User · Answer

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  gt   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  gt   0   BEGIN     IF  TempCounter  gt  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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server-2005