s是開始日期，e是結束日期
create or replace function workday(s in date,e in date) RETURN INTEGER
as
result integer;
begin
select count(*) into result from (
select to_char(s+level-1,'d') w,s+level-1 from dual connect by level<=(e-s+1)
)
where w not in ('1','7');
return result;
end workday;
/

SQL> select workday(to_date('20131101','yyyymmdd'),to_date('20131115','yyyymmdd')) workday from dual;

WORKDAY
----------
11

SQL> select workday(to_date('20131101','yyyymmdd'),to_date('20131125','yyyymmdd')) workday from dual;

WORKDAY
----------
17

SQL> select workday(to_date('20131101','yyyymmdd'),to_date('20131130','yyyymmdd')) workday from dual;

WORKDAY
----------
21

計算兩個日期之間所有的工作日

相關文章