計算兩個日期之間的工作日天數(不包括這兩個日期,不包括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 函數是不把開始時間和結束時間計入工作日時間的,即使 這兩個時間都是工作日時間。