[Translated from mos] computing the workday days between two dates in the Oracle database, mosoracle

[Translated from mos] computing the workday days between two dates in the Oracle database, mosoracle

Calculate the number of working days between two dates (excluding those two dates, excluding weekend)

Reference:
How to Compute Business Days for a Date Range in SQL or PLSQL (Doc ID 1014162.6)

There are two methods:

The first is to use SQL statements:

SQL> SELECT (TO_NUMBER (TRUNC (to_date ('1970-04-22 ', 'yyyy-mm-dd'), 'D ') -TRUNC (to_date ('1970-04-21 ', 'yyyy-mm-dd') + 6, 'D')/7*5) + 2 MOD (7-TO_NUMBER (TO_CHAR (to_date ('1970-04-21 ', 'yyyy-mm-dd'), 'D'), 6) + 3 LEAST (TO_NUMBER (TO_CHAR (to_date ('1970-04-22 ', 'yyyy-mm-dd'), 'D')-2, 5) days 4 FROM dual; DAYS ---------- 1 -- returned from the preceding select statement:-04-22 is Wednesday, and is Tuesday SQL> SELECT (TO_NUMBER (TRUNC (to_d Ate ('1970-04-27 ', 'yyyy-mm-dd'), 'D')-TRUNC (to_date ('1970-04-24', 'yyyy-mm-dd ') + 6, 'D')/7*5) + MOD (7-TO_NUMBER (TO_CHAR (to_date ('2017-04-24 ', 'yyyy-mm-dd '), 'D'), 6) + LEAST (TO_NUMBER (TO_CHAR (to_date ('2017-04-27 ', 'yyyy-mm-dd'), 'D')-2, 5) days FROM dual 2 3 4 5/DAYS ---------- 1 -- as returned by the preceding select statement:-04-27 is Monday, and is Friday. That is to say, the SQL function only sets the start time (if it is a workday) or the end time (if it is a workday) is counted into the workday time.

 

The second is to use functions:

Create or replace function num_Business_Days (start_date IN date, end_date IN date) RETURN number IS currdate date: = start_date;/* holds the next date */theDay varchar2 (10 ); /* day of the week for currdate */countBusiness number: = 0; /* counter for business days */BEGIN/* start date must be earlier than end date */IF end_date-start_date <= 0 then return (0); end if; LOOP/* go to the next Day */currdate: = TO_DATE (currdate + 1);/* finished if end_date is reached */exit when currdate = end_date;/* what day of the week is it? */SELECT TO_CHAR (currdate, 'fmday') INTO theDay FROM dual; /* count it only if it is a weekday */IF theDay <> 'saturday' AND theDay <> 'sunday' THEN countBusiness: = countBusiness + 1; end if; end loop; RETURN (countBusiness); END;/SQL> SELECT num_Business_Days (to_date ('2017-04-21 ', 'yyyy-mm-dd'), to_date ('2017-04-22 ', 'yyyy-mm-dd') "Business Days" FROM dual; Business Days ----------- 0SQL> SELECT Num_Business_Days (to_date ('2017-04-24 ', 'yyyy-mm-dd'), to_date ('2017-04-27', 'yyyy-mm-dd ')) "Business Days" FROM dual; Business Days ------------- 0 that is to say: the num_Business_Days function does not include the start time and End Time in the workday time, even if both are workday time.