计算两个日期之间的工作日天数(不包括这两个日期,不包括weekend)
参考自:
How to Compute Business Days for a Date Range in SQL or PLSQL (Doc ID 1014162.6)
有两个方法:
第一个是 使用sql语句:
SQL> SELECT ((TO_NUMBER(TRUNC(to_date('2015-04-22','yyyy-mm-dd'), 'D') - TRUNC(to_date('2015-04-21','yyyy-mm-dd') + 6, 'D'))) / 7 * 5) +
2 MOD(7 - TO_NUMBER(TO_CHAR(to_date('2015-04-21','yyyy-mm-dd'), 'D')), 6) +
3 LEAST(TO_NUMBER(TO_CHAR(to_date('2015-04-22','yyyy-mm-dd'), 'D')) - 2, 5) days
4 FROM dual;
DAYS
----------
1
--如上select 返回:1,2015-04-22是周三,2015-04-21是周二
SQL> SELECT ((TO_NUMBER(TRUNC(to_date('2015-04-27','yyyy-mm-dd'), 'D') - TRUNC(to_date('2015-04-24','yyyy-mm-dd') + 6, 'D'))) / 7 * 5) +
MOD(7 - TO_NUMBER(TO_CHAR(to_date('2015-04-24','yyyy-mm-dd'), 'D')), 6) +
LEAST(TO_NUMBER(TO_CHAR(to_date('2015-04-27','yyyy-mm-dd'), 'D')) - 2, 5) days
FROM dual 2 3 4
5 /
DAYS
----------
1
--如上select返回:1,2015-04-27是周一,2015-04-24是周五
也就是说: sql 函数是仅仅把开始时间(若是工作日)或者 结束时间(若是工作日)计入工作日时间。
第二个是使用函数:
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('2015-04-21','yyyy-mm-dd'),to_date('2015-04-22','yyyy-mm-dd'))"Business Days" FROM dual;
Business Days
-------------
0
SQL> SELECT num_Business_Days(to_date('2015-04-24','yyyy-mm-dd'),to_date('2015-04-27','yyyy-mm-dd'))"Business Days" FROM dual;
Business Days
-------------
0
也就是说: num_Business_Days 函数是不把开始时间和结束时间计入工作日时间的,即使 这两个时间都是工作日时间。