Tags

, ,

How can we calculate the number of business days (Monday – Friday) between two given dates….

This is a question often asked in the SQL community.

Two solutions for this problem:


Solution 1:

Calculate the number of days between two date values and subtract the number of Saturdays and Sundays in between.

WITH DATA AS (SELECT TO_DATE('05.09.2011','dd.mm.yyyy') date_from                    ,TO_DATE('31.10.2011','dd.mm.yyyy') date_to                FROM dual)
SELECT date_to - date_from + 1
     - (((NEXT_DAY(date_to-7,'Saturday') - NEXT_DAY(date_from,'Saturday'))/7) + 1)
     - (((NEXT_DAY(date_to-7,'Sunday')   - NEXT_DAY(date_from,'Sunday'))/7) + 1)
  FROM data
/

Solution 2:

Count the number of days that are neither Saturdays nor Sundays between two date values using a recursive query.

SELECT COUNT(DECODE(TO_CHAR(TO_DATE('05.09.2011','dd.mm.yyyy') + (ROWNUM - 1),'DY'),'SAT',NULL,'SUN',NULL,'1'))
   FROM dual
 CONNECT BY ROWNUM <= (TO_DATE('31.10.2011','dd.mm.yyyy') - TO_DATE('05.09.2011','dd.mm.yyyy') + 1) 
/