Oracle – Calculating Business Days Between Two Dates (With Holiday Exclusion)

If you search the web on how to calculate the number of business days between two dates in Oracle, you would see so many results. Here is one more with some explanation to go with it. There is no magic to it. It is simple and straight-forward and this is as basic as it can get.

Example requirement

Let us say that this is the business requirement – “Today, I am calculating the number for days between two dates to get the response time in days but I would really like to exclude the weekends to get an accurate picture”.

I am going to simulate this business scenario below where it shows an activity’s start and end date

WITH response_time
AS
(
    SELECT
        sysdate - 600 AS start_date,
        sysdate - 500 AS end_date
    FROM dual
)
SELECT
    a.*,
    (a.end_date - a.start_date) AS reponse_time_in_days
FROM response_time a

The results are simple enough:

START_DATE END_DATE REPONSE_TIME_IN_DAYS
7/1/2012 15:15 10/9/2012 15:15 100

Excluding “weekends”

Now, how do we take out the “weekend days” out from the “RESPONSE_TIME_IN_DAYS”?

  • First, we need to know all the dates in between the start and end dates.
  • Then, we need to determine which dates among those are either a Saturday or a Sunday
  • Finally, we count the resulting dates

Let us go about doing this. Initially, we are going to use a brute force approach and generate all “days” after 1/1/1950 for 100 years (roughly till Dec, 2050) assuming our start and end dates will fall within that range (we will refine the solution later)

    SELECT
        to_date('01/01/1950','MM/DD/YYYY') + seq AS day_date,
        to_char(to_date('01/01/1950','MM/DD/YYYY') + seq , 'D') day_of_week
    FROM
    (
        SELECT ROWNUM-1 seq
        FROM   ( SELECT 1
                 FROM   dual
                 CONNECT BY LEVEL <= 365 * 100 --100 years
               )
    )
    ORDER BY 1

This query is literally going to generate 100 years’ worth of dates, one per day from 1/1/1950

DAY_DATE DAY_OF_WEEK
1-Jan-1950 1
2-Jan-1950 2
3-Jan-1950 3
4-Jan-1950 4
5-Jan-1950 5
6-Jan-1950 6
7-Jan-1950 7
…..more rows inbetween
3-Dec-2049 6
4-Dec-2049 7
5-Dec-2049 1
6-Dec-2049 2

Day 1 and 7 make the weekend

In the table above, DAY_OF_WEEK 7 and 1 correspond to Saturday and Sunday respectively (whose corresponding dates, we want to exclude). More specifically, the numbering is as follows:

Day of Week Day
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

Exclude the weekends

Below, I have put the above pieces together

WITH days
AS
(
    SELECT
        to_date('01/01/1950','MM/DD/YYYY') + seq AS day_date,
        to_char(to_date('01/01/1950','MM/DD/YYYY') + seq , 'D') day_of_week
    FROM
    (
        SELECT ROWNUM-1 seq
        FROM   ( SELECT 1
                 FROM   dual
                 CONNECT BY LEVEL <= 365 * 100 --100 years ) ) ORDER BY 1 ), response_time AS ( SELECT sysdate - 600 AS start_date, sysdate - 500 AS end_date FROM dual ) SELECT end_date - start_date AS days_inbetween, count(1) business_days_inbetween FROM response_time resp, days WHERE --------------------------------------------------- --Get only the dates between our start and end dates --------------------------------------------------- days.day_date >= resp.start_date
    AND days.day_date <= resp.end_date
    ---------------------------------------------------
    --...then exclude Sat and Sun
    ---------------------------------------------------
    AND days.day_of_week NOT IN (7,1)

And the results are as follows:

DAYS_INBETWEEN BUSINESS_DAYS_INBETWEEN
100 72

Exclude holidays too, please!

That was easy enough. Different countries and regions have different holidays. It is not a good idea to hard-code the list of holidays in your query. Now, if we had a calendar table which had the holidays, just add that in to the end of the query (note the highlighted part). The Holiday_Table could have just three columns RegionHolidayDate which is a date and a Description to describe the holiday.

WHERE
    ---------------------------------------------------
    --Get only the dates between our start and end dates
    ---------------------------------------------------
    days.day_date >= resp.start_date
    AND days.day_date <= resp.end_date
    ---------------------------------------------------
    --...then exclude Sat and Sun
    ---------------------------------------------------
    AND days.day_of_week NOT IN (7,1)
    ---------------------------------------------------
    --...then exclude week-day holidays
    ---------------------------------------------------
    AND NOT EXISTS
        (
            SELECT holiday
            FROM holidays_table ht
            WHERE ht.holidaydate = days.day_date
--Optionally include Region!
        )

Need a more efficient solution?

While this works, it can certainly be tweaked a bit more. We do not need to generate 100 years’ worth of dates – we need to generate exactly the dates in between the start date and end date.

    SELECT
        v_Begin_Date + seq AS day_date,
        to_char(v_Begin_Date + seq , 'D') day_of_week
    FROM
    (
        SELECT ROWNUM-1 seq
        FROM   ( SELECT 1
                 FROM   dual
    --number of rows should be exactly the number of days between begin and end dates
                 CONNECT BY LEVEL <= (v_End_Date – v_Begin_Date) + 1
               )
    )
    ORDER BY 1

Notice the highlighted variable substitutions and you will see that we have actually made the day generator SQL simpler and more efficient too.

Complete solution as function:

Based on a comment from Damodar, I have created a function that does the calculation based on a begin date and end date (defined as parameters). The usage example is at the very top:

/*
--Sample usage:

DECLARE
    v_Begin_Date DATE := TO_DATE('01/01/2016','DD/MM/YYYY');
    v_End_Date DATE := TO_DATE('31/12/2016','DD/MM/YYYY');
    v_BusDaysInbetween NUMBER := 0;
BEGIN
    v_BusDaysInbetween := fn_GetBusinessDaysInterval(v_Begin_Date, v_End_Date);
    
    DBMS_OUTPUT.PUT_LINE('Business days inbetween is : ' || TO_CHAR(v_BusDaysInbetween));
END;

*/
CREATE OR REPLACE
FUNCTION fn_GetBusinessDaysInterval
(
    v_Begin_Date DATE,
    v_End_Date DATE    
)
    RETURN NUMBER
AS
    v_DaysInbetween NUMBER := 0;
    v_BusDaysInbetween NUMBER := 0;
        
BEGIN
    WITH days
    AS
    (
        SELECT
            v_Begin_Date + seq AS day_date,
            to_char(v_Begin_Date + seq , 'D') day_of_week
        FROM
        (
            SELECT ROWNUM-1 seq
            FROM   ( SELECT 1
                     FROM   dual
                     --number of rows should be exactly the number of days between begin and end dates
                     CONNECT BY LEVEL <= (v_End_Date - v_Begin_Date) + 1
                   )
        )
        ORDER BY 1
    )
    SELECT
        v_End_Date - v_Begin_Date AS days_inbetween,
        count(1) business_days_inbetween
    INTO
        v_DaysInbetween,
        v_BusDaysInbetween        
    FROM
        days
    WHERE
        ---------------------------------------------------
        --...then exclude Sat and Sun
        ---------------------------------------------------
        days.day_of_week NOT IN (7,1)
        ---------------------------------------------------
        --...then exclude week-day holidays
        ---------------------------------------------------
        /*
        AND NOT EXISTS
            (
                SELECT holiday
                FROM holidays_table ht
                WHERE ht.holidaydate = days.day_date
                --Optionally include Region!
            )*/;        

    DBMS_OUTPUT.PUT_LINE('Days inbetween = ' || TO_CHAR(v_DaysInbetween));
    DBMS_OUTPUT.PUT_LINE('Days inbetween = ' || TO_CHAR(v_BusDaysInbetween));
    
    RETURN v_BusDaysInbetween;        
END;        

Enjoy!

Advertisements

6 thoughts on “Oracle – Calculating Business Days Between Two Dates (With Holiday Exclusion)

  1. Thanks for this nice post! I wonder if it is possible to make a function that only returns the BUSINESS_DAYS_INBETWEEN (in your case). I have tried tried to adapt your code as below but it didn’t work. Can you help me with this please? I am new in writing functions; sorry if there is a simple mistake in the code. Thanks.

    create or replace function working_days_2 (start_date in date, end_date in date)
    return number as number_of_working_days
    begin

    with
    days as
    (
    select
    to_date(’01/07/2016′,’DD/MM/YYYY’) + seq as day_date,
    to_char(to_date(’01/07/2016′,’DD/MM/YYYY’) + seq , ‘D’) day_of_week
    from
    (
    select rownum-1 seq
    from ( select 1 from dual connect by level = resp.start_date –Get only the dates between our start and end dates
    and days.day_date <= resp.end_date
    and days.day_of_week not in (7,1) –…then exclude Sat and Sun
    and not exists –…then exclude week-day holidays
    ( select holiday_date from holidays h where h.holiday_date= days.day_date )
    ;
    return number_of_working_days;
    end working_days_2;

    1. Damodar, Thanks for the feed back. Please see the last section now. I have updated the blog post to include the function that does the calculation.

  2. EXCELLENT!! I have been looking for something this complete and to-the-point for days now. Searching for an Oracle equivalent for the Excel NETWORKDAYS function has led me through vaguely answered StackOverflow posts and some overly-complex functions. When I came across this and it worked IMMEDIATELY for me I could have danced a jig. And, to find something posted so recently, I didn’t have to worry about if I was using some deprecated methodology that would drive me crazy during the next upgrade was a HUGE bonus.

    VERY, VERY well done, thank you!

  3. Ryan, Thank you for the great feedback. Frankly, it made my day. I had the post up for a while but the function itself at the end was recently added based on Damodar’s feedback. There is nothing in there that Oracle would deprecate soon.

    You are welcome.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s