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