I was recently approached by a colleague at work with a question if I know an Oracle’s Equivalent for Networkdays Function in Excel. I did not have an immediate answer, but a few minutes later I was able to find a way.
First, let’s define what the Networkdays function does. It should give the number of days between two given dates excluding Sundays and Saturdays.
If we had a calendar table (with a work_day column) the problem would be solved fairly easily:
SELECT COUNT(1) FROM calendar_table WHERE TO_CHAR(work_day, 'D') BETWEEN 2 AND 6
To ensure that this code works for different geographic regions, it would be safer to add nls_date_language attribute as follows:
SELECT COUNT(1) FROM calendar_table WHERE TO_CHAR(work_day, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
In absence of the calendar_table, we can mimic it using a standard method for a numeric/date range generator. Let say, we need to generate a range of dates between 15-May-2015 and 20-Aug-2015:
SELECT DATE'2015-05-15'+LEVEL-1 AS day FROM dual CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20'; Result (partial): DAY --------- 15-MAY-15 16-MAY-15 17-MAY-15 18-MAY-15 ... 17-AUG-15 18-AUG-15 19-AUG-15 20-AUG-15
So after excluding the weekend days, the counting task becomes trivial:
SELECT COUNT(1) FROM dual WHERE TO_CHAR(DATE'2015-05-15'+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6 CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20'; Result: COUNT(1) -------- 70
Finally, we are ready to package it all together and create a function:
CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER AS v_result INTEGER; BEGIN SELECT COUNT(1) INTO v_result FROM dual WHERE TO_CHAR(p_date1+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6 CONNECT BY TRUNC(p_date1)+LEVEL-1 <= TRUNC(p_date2); RETURN v_result; END; /
This is a quite elegant solution, though I had a feeling that it is a bit over-complicated. A few minutes later, I derived a non-SQL approach:
CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER AS v_date1 DATE:=CASE TO_CHAR(p_date1, 'D', 'nls_date_language=AMERICAN') WHEN 1 THEN TRUNC(p_date1)+1 WHEN 6 THEN TRUNC(p_date1)+2 ELSE TRUNC(p_date1) END; v_date2 DATE:=CASE TO_CHAR(p_date2, 'D', 'nls_date_language=AMERICAN') WHEN 1 THEN TRUNC(p_date2)+1 WHEN 6 THEN TRUNC(p_date2)+2 ELSE TRUNC(p_date2) END; BEGIN IF p_date1>p_date2 THEN RETURN 0; ELSIF p_date1=p_date2 THEN RETURN 1; ELSE RETURN (TRUNC(v_date2, 'D') - TRUNC(v_date1, 'D')) * 5/7 + TO_CHAR(v_date2, 'D', 'nls_date_language=AMERICAN') - TO_CHAR(v_date1, 'D', 'nls_date_language=AMERICAN'); END IF; END; /
A few comments for better understanding the above code:
1) If one(or both) function parameter values is a week-end day, we change it to the following Monday as it does not change the number of non-weekend days in the given range.
2) TRUNC function with ‘D’ argument returns the first day of the week, so we can expect the number of days between two Sundays (or whatever the first day is) to be a multiple of 7, and so 5/7 gives us the number of non-weekend days.
3) TO_CHAR(v_date2, ‘D’, ‘nls_date_language=AMERICAN’) – TO_CHAR(v_date1, ‘D’, ‘nls_date_language=AMERICAN’) – is an adjustment to the result based on the day of the week of each of the date range parameters.
Quick test for the new function – Find number of work days before the end of the year:
SELECT SYSDATE+LEVEL-1 AS day, TO_CHAR(sysdate+level-1, 'DY') AS day_of_week, networkdays(sysdate+level-1, date'2015-12-31') netdays FROM dual CONNECT BY LEVEL<=15 Result: DAY DAY_OF_WEEK NETDAYS --------- ------------ ---------- 10-OCT-15 SAT 58 11-OCT-15 SUN 58 12-OCT-15 MON 58 13-OCT-15 TUE 57 14-OCT-15 WED 56 15-OCT-15 THU 55 16-OCT-15 FRI 54 17-OCT-15 SAT 53 18-OCT-15 SUN 53 19-OCT-15 MON 53 20-OCT-15 TUE 52 21-OCT-15 WED 51 22-OCT-15 THU 50 23-OCT-15 FRI 49 24-OCT-15 SAT 48
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.