Calculate difference between 2 dates in SQL, excluding weekend days

Question

I would like to build an SQL query which calculates the difference between 2 dates, without counting the week-end days in the result.

Is there any way to format the dates to obtain this result ? For example for Oracle database :

select sysdate - creation_dttm from the_table

Answer 1

If your company has a DATES table listing the date and relative work day, as most companies do, you could build a temp table that excludes duplicate relative work days that will also give you the ability to exclude holidays from your calculations as well as weekends.

Answer 2

I have found several of the answers on this thread to not do what they claim. After some experimentation, testing and adjusting, I have this to contribute.

declare @firstdate Date
declare @seconddate Date

set @firstDate = convert(date, '2016-03-07')
set @seconddate = convert(date, '2016-04-04')


select (datediff(dd, @firstDate, @secondDate)) - 
    (( DateDiff(wk, @firstDate, @secondDate) * 2) - 
      case when datepart(dw, @FirstDate) = 7 then 1 else 0 end -
      case when datepart(dw, @secondDate) = 7 then -1 else 0 end)

Test harness included - you can just adjust the two dates and run your own tests. This assumes that the difference between two adjacent weekday dates is 1. If your country uses different days to signify weekend, then you will have to set the date-base accordingly so your "Saturday" is 7, and your "sunday" is 1.

Answer 3

You can try this:

SELECT
    Id,
    DATEDIFF(d, datefrom, dateto) AS TotDays,
    DATEDIFF(wk, datefrom, dateto) AS Wkds,
    DATEDIFF(d, datefrom, dateto) - DATEDIFF(wk, datefrom, dateto) AS Days
FROM
    YOURTABLE

Answer 4

• Calculates the difference in days between the two dates
• Calculates the difference in week numbers and year numbers, subtracts the week numbers and then multiplies the result by 2 to calculate number of non-workdays between the two dates. If year numbers are different calculation of 52 * year difference + week number.

((sysdate - ced.created_dt) + ((((to_char(ced.created_dt,'IW') - ((to_char(sysdate,'YY') - to_char(ced.created_dt,'YY'))* 52)) - to_char(to_char(sysdate,'IW')))) * 2)) duration_in_weekdays

Answer 5

You should try with a function :

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Test :

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Result :

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |

Answer 6

Here is an example:
There are four variables, the first two are self-explanatory, just enter the date in YYYYMMDD format, the 3rd one is to set the number of normal work days in a given week, so if a site works 6 days a week, set it to 6, five days a week enter 5, etc. Finally, the DATE_SEQ_NORML_FACTOR should be 1 when running against Oracle. This is to line up the Julian date to equal to 1 on Monday, 2 on Tuesday, etc when applying the MOD 7. Other DB will probably have different values between 0 and 6, so test it out before you use against other DBs.

Here are the limitations: 1. This formula assumes the first day of the week is MONDAY. 2. This formula assumes all days within the same week are CONTINUOUS. 3. This formula will work ONLY when the two dates involved in the calculation falls on a week day or work day, eg. the "Start Date" on a SATURDAY when the location works only MON-FRI will not work.

SELECT

  &&START_DATE_YYYYMMDD "Start Date", --in YYYYMMDD format

  &&END_DATE_YYYYMMDD "End Date", --in YYYYMMDD format

  &&WK_WORK_DAY_CNT "Week Work Day Count", --Number of work day per week

  &&DATE_SEQ_NORML_FACTOR "Normalization Factor", --set to 1 when run in Oracle

  CASE

  WHEN

    FLOOR( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 ) =

    FLOOR( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR / 7 )

  THEN(

    TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) -

    TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + 1

  )

  ELSE(

    (

      &&WK_WORK_DAY_CNT - MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) + 1

    ) +

    MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 ) +

    (

      (

        (

          TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) -

          MOD( TO_CHAR( TO_DATE( &&END_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 )

        ) -

        (

          TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) +

          (

            7 -

            (

              MOD( TO_CHAR( TO_DATE( &&START_DATE_YYYYMMDD , 'YYYYMMDD') , 'J' ) + &&DATE_SEQ_NORML_FACTOR , 7 )

            )

          )

        )

      ) / 7 * &&WK_WORK_DAY_CNT

    )

  ) END "Week Day Count"

FROM DUAL

Answer 7

I've updated @JOpuckman's function to take into account that different regions don't always have a weekend of Saturday and Sunday. Here's the code in case anyone else needs to apply this globally;

DECLARE @FirstDate DateTime
DECLARE @SecondDate DateTime

SET @FirstDate = '08-20-2012'
SET @SecondDate = '08-24-2012'

DECLARE @range INT;
DECLARE @WeekendDayNameStart VARCHAR(50) 
DECLARE @WeekendDayNameEnd VARCHAR(50)

SET @WeekendDayNameStart = 'FRIDAY'
SET @WeekendDayNameEnd = (
    SELECT CASE @WeekendDayNameStart
        WHEN 'SUNDAY' THEN 'MONDAY'
        WHEN 'MONDAY' THEN 'TUESDAY'
        WHEN 'TUESDAY' THEN 'WEDNESDAY'
        WHEN 'WEDNESDAY' THEN 'THURSDAY'
        WHEN 'THURSDAY' THEN 'FRIDAY'
        WHEN 'FRIDAY' THEN 'SATURDAY'
        WHEN 'SATURDAY' THEN 'SUNDAY'
    END
)

DECLARE @NumWorkDays INT

SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); 
SET @NumWorkDays = (
SELECT  
@range / 7 * 5 + @range % 7 - ( 
    SELECT COUNT(*)  
    FROM ( 
        SELECT 1 AS d 
        UNION ALL SELECT 2  
        UNION ALL SELECT 3  
        UNION ALL SELECT 4  
        UNION ALL SELECT 5  
        UNION ALL SELECT 6  
        UNION ALL SELECT 7 
        ) weekdays 
    WHERE d <= @range % 7  
    AND DATENAME(WEEKDAY, @SecondDate - d) IN (@WeekendDayNameStart, @WeekendDayNameEnd))
);

-- Calculate whether the current date is a working day
DECLARE @CurDateExtra INT
SET @CurDateExtra = 
(
    CASE DATENAME(WEEKDAY, @SecondDate)
    WHEN @WeekendDayNameStart THEN 0
    WHEN @WeekendDayNameEnd THEN 0
    ELSE 1
    END
)

SET @NumWorkDays = @NumWorkDays + @CurDateExtra
SELECT @NumWorkDays

Answer 8

I have found another way to do calculate the difference, by using only SQL :

select sysdate - creation_dttm
- 2 * (to_char(sysdate, 'WW') - to_char(creation_dttm, 'WW'))
from the_table

Answer 9

We've incorporated the following logic into several reports with great success. Sorry, I no longer recall the source of this script to give them credit.

DECLARE @range INT;

SET @range = DATEDIFF(DAY, @FirstDate, @SecondDate); 
SET @NumWorkDays = (
    SELECT  
    @range / 7 * 5 + @range % 7 - ( 
        SELECT COUNT(*)  
        FROM ( 
            SELECT 1 AS d 
            UNION ALL SELECT 2  
            UNION ALL SELECT 3  
            UNION ALL SELECT 4  
            UNION ALL SELECT 5  
            UNION ALL SELECT 6  
            UNION ALL SELECT 7 
            ) weekdays 
        WHERE d <= @range % 7  
        AND DATENAME(WEEKDAY, @SecondDate - d) IN ('Saturday', 'Sunday'))
    );

Answer 10

From a previous post:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)