create or replace function Howworkday(begindate date,enddate date) return integer as v_b date; v_Num integer; begin v_b:=begindate; v_num:=0; while v_b <= enddate loop If to_char(v_b,'d') =1 or to_char(v_b,'d') =7 then v_num := v_num; Else v_num:=v_num+1; End if; v_b := v_b+ 1; End loop; return v_num; End;select Howworkday(to_date('20060101','yyyymmdd'),to_date('20060201','yyyymmdd')) from dual23
Select Round(To_Number(To_Date('20070203', 'yyyymmdd') -To_Date('20060203', 'yyyymmdd')) , 1 ) From Dual;
SQL> select trunc(dd/7)*5+mod(dd,7)-abs(decode(to_char(to_date('2006-03-01','yyyy-mm-dd'),'d') 2 ,7,1,1,2,0)-decode(to_char(to_date('2006-03-22','yyyy-mm-dd'),'d'),7,1,1,2,0)) workday 3 from ( 4 select to_date('2006-03-22','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd') dd 5 from dual); WORKDAY ---------- 15SQL> SQL> select trunc(dd/7)*5+mod(dd,7)-abs(decode(to_char(to_date('2006-03-01','yyyy-mm-dd'),'d') 2 ,7,1,1,2,0)-decode(to_char(to_date('2006-03-25','yyyy-mm-dd'),'d'),7,1,1,2,0)) workday 3 from ( 4 select to_date('2006-03-25','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd') dd 5 from dual); WORKDAY ---------- 17SQL>
select count(*) from ( select rownum-1 rnum from all_objects where rownum <= to_date('2006-03-26','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd')+1 ) where to_char(to_date('2006-03-01','yyyy-mm-dd')+rnum-1,'D') not in ('6','7')--------- 18
但实际情况并不是这样,有每年的法定节假日,有五一、十一的串休,不同的公司还有自身的休息日(也有每周六天工作的),这样我们通常建一个表来存储和维护这些休息日,然后在查询时到表中查找两段时间内休息日的天数,剩下的时间就是工作日。
Howworkday(begindate date,enddate date)
return integer
as
v_b date;
v_Num integer;
begin
v_b:=begindate;
v_num:=0;
while v_b <= enddate loop
If to_char(v_b,'d') =1 or to_char(v_b,'d') =7
then
v_num := v_num;
Else
v_num:=v_num+1;
End if;
v_b := v_b+ 1;
End loop;
return v_num;
End;select Howworkday(to_date('20060101','yyyymmdd'),to_date('20060201','yyyymmdd'))
from dual23
From Dual;
2 To_Date('20060203', 'yyyymmdd')) , 1 )
3 From Dual;ROUND(TO_NUMBER(TO_DATE('20070
------------------------------
365
2 ,7,1,1,2,0)-decode(to_char(to_date('2006-03-22','yyyy-mm-dd'),'d'),7,1,1,2,0)) workday
3 from (
4 select to_date('2006-03-22','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd') dd
5 from dual); WORKDAY
----------
15SQL>
SQL> select trunc(dd/7)*5+mod(dd,7)-abs(decode(to_char(to_date('2006-03-01','yyyy-mm-dd'),'d')
2 ,7,1,1,2,0)-decode(to_char(to_date('2006-03-25','yyyy-mm-dd'),'d'),7,1,1,2,0)) workday
3 from (
4 select to_date('2006-03-25','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd') dd
5 from dual); WORKDAY
----------
17SQL>
select rownum-1 rnum from all_objects
where rownum <= to_date('2006-03-26','yyyy-mm-dd')-to_date('2006-03-01','yyyy-mm-dd')+1
)
where to_char(to_date('2006-03-01','yyyy-mm-dd')+rnum-1,'D')
not in ('6','7')---------
18