不使用function计算给定两个日期之间的工作日个数

可能这就是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