计算两个日期之间的工作日天数(不包括这两个日期，不包括weekend)

有两个方法：

第一个是 使用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 函数是不把开始时间和结束时间计入工作日时间的，即使 这两个时间都是工作日时间。