可能这就是Oracle的work_days函数的算法吧
Calculating Working Days Without Using a Function
This tip comes from Mike Selvaggio, Senior DB Architect and DBA at Orsel Consulting Inc. in North Brunswick, NJ.
If you need to calculate working days between two dates and you can't create the Oracle-recommended working days function, here is SQL that can accomplish the same task:
SQL> set linesize 100
SQL> desc date_test
Name Null? Type
START_DT DATE
END_DT DATE
SQL> select * from date_test
2 .
SQL> /
START_DT END_DT
--------- ---------
13-DEC-02 18-DEC-02
17-DEC-02 19-DEC-02
18-DEC-02 23-DEC-02
26-DEC-02 28-DEC-02
SQL> select start_dt, end_dt, end_dt - start_dt age,
work_days(start_dt, end_dt) from date_test;
START_DT END_DT AGE WORK_DAYS(START_DT,END_DT)
--------- --------- ---------- --------------------------
13-DEC-02 18-DEC-02 5 3
17-DEC-02 19-DEC-02 2 2
18-DEC-02 23-DEC-02 5 3
26-DEC-02 28-DEC-02 2 1
SQL> get workingdays
代码：
select start_dt, end_dt, trunc(end_dt - start_dt) age,
(trunc(end_dt - start_dt) - (
(case
WHEN (8-to_number(to_char(start_dt,'D') )) > trunc(end_dt -start_dt)+1
THEN 0
ELSE
trunc( (trunc(end_dt - start_dt) -(8-to_number(to_char(start_dt,'D') ))) / 7 ) + 1 END) +
(case
WHEN mod(8-to_char(start_dt,'D'),7) > trunc(end_dt - start_dt)-1
THEN 0
ELSE
trunc( (trunc(end_dt-start_dt) - (mod(8-to_char(start_dt,'D'),7)+1)) / 7 ) + 1
END) ) ) workingdays
from date_test
SQL> /
START_DT END_DT AGE WORKINGDAYS
--------- --------- ---------- -----------
13-DEC-02 18-DEC-02 5 3
17-DEC-02 19-DEC-02 2 2
18-DEC-02 23-DEC-02 5 3
26-DEC-02 28-DEC-02 2 1
|
