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)
/