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 Region, HolidayDate 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!