Take the 2-minute tour ×

Could you please help me out how to calculate the no. of business days between start date and end date by excluding weekends & business specific holidays. Let's say if we take start date, end date and custom holiday table with list of holiday dates as mentioned below.

Start date (mm/dd/yyyy hh24:mi:ss) : '10/15/2012 08:00:00'

End date (mm/dd/yyyy hh24:mi:ss) : '10/23/2012 10:30:00'

holiday_table.business_holiday_date values (mm/dd/yyyy):




Need to calculate business days by taking into consideration of the time portion of dates in the calculation (so can expect business days with fractions as well ex. 1.25, 3.7 etc)

Appreciate if you can help me on this ASAP.


share|improve this question
Hello James and welcome to StackOverflow. Consider expanding your question with what have you attempted so far. See How to Ask for details. –  bytebuster Oct 20 '12 at 0:15
add comment

2 Answers


SELECT(end_date - start_date) - 
(select count(*) 
 from holiday_table 
 where business_holiday_date >= start_date
 and business_holiday_date <= end_date)
FROM dual

Here is a fiddle

share|improve this answer
add comment

I have a function that use only 2 dates but you can improve it. Here you go...

  1. First check how many days you got in the holiday table, excluding weekend days.
  2. Get business days (MON to FRI) between the 2 dates and after that subtract the holiday days.

    create or replace
    FUNCTION calculate_business_days (p_start_date IN DATE, p_end_date IN DATE)
            RETURN NUMBER IS
            v_holidays     NUMBER;
            v_start_date   DATE   := TRUNC (p_start_date);
            v_end_date     DATE   := TRUNC (p_end_date);
            IF v_end_date >= v_start_date
                    SELECT COUNT (*)
                    INTO v_holidays
                    FROM holidays
                    WHERE day BETWEEN v_start_date AND v_end_date
                    AND day NOT IN (
                            SELECT hol.day 
                            FROM holidays hol 
                            WHERE MOD(TO_CHAR(hol.day, 'J'), 7) + 1 IN (6, 7)
            RETURN   GREATEST (NEXT_DAY (v_start_date, 'MON') - v_start_date - 2, 0)
                 +   (  (  NEXT_DAY (v_end_date, 'MON')
                         - NEXT_DAY (v_start_date, 'MON')
                      / 7
                   * 5
                 - GREATEST (NEXT_DAY (v_end_date, 'MON') - v_end_date - 3, 0)
                 - v_holidays;
                    RETURN NULL;
            END IF;
    END calculate_business_days;

After that you can test it out, like:

            calculate_business_days('21-AUG-2013','28-AUG-2013') as business_days 
    from dual;
share|improve this answer
add comment

Your Answer


