oracle如何计算某段时间内的工作日,oracle 一段时间内有几个工作日（天数）

create or replace

FUNCTION GETWORKDAY3

( sdate IN DATE

, edate IN DATE

) RETURN NUMBER AS

holidays number:=0;

st Date;

en Date;

BEGIN

for h in (select holiday_id hid from T_ATTE_HOLIDAYS) loop

select DATE_START into st from T_ATTE_HOLIDAYS where holiday_id=h.hid;

select DATE_END into en from T_ATTE_HOLIDAYS where holiday_id=h.hid;

if(st>sdate and en>edate and edate>=st) then

begin

holidays:=(edate-st)+1+holidays-workend(st,edate);

-- holidays:=(st-sdate)-workend(sdate,st)+holidays;

dbms_output.put_line('left'||to_char(edate-st));

end;

Elsif(st<=sdate and en>edate) then

begin

holidays:=(edate-sdate)+1-workend(edate,sdate)+holidays;

--  holidays:=(st-sdate)+(en-edate)+holidays-workend(sdate,st)-workend(edate,en);

dbms_output.put_line('midder');

end;

Elsif(st

begin

holidays:=(en-sdate)+1-workend(sdate,en)+holidays;

-- holidays:=(edate-en)-workend(en,edate)+holidays;

dbms_output.put_line('right');

--  dbms_output.put_line(to_char(st,'yyyy-MM-dd')||'

end;

Elsif(st>=sdate and en<=edate) then

begin

holidays:=(en-st)-workend(st,en)+1+holidays;

-- holidays:=(st-sdate)+(edate-en)-workend(sdate,st)-workend(en,edate)+holidays;

dbms_output.put_line('outer'||to_char(en-st)||to_char(workend(st,en)));

end;

else

begin

holidays:=0+holidays;

-- holidays:=(edate-sdate)+1-workend(sdate,edate);

dbms_output.put_line('else');

end;

end if;

end loop;

RETURN (edate-sdate)+1-holidays-workend(sdate,edate);

--return holidays;

END GETWORKDAY3;

create or replace FUNCTION WORKEND

( sdate IN DATE

, edate IN DATE

) RETURN NUMBER AS

v_date Date;

v_days number:=0;

BEGIN

v_date:=sdate;

while v_date<=edate loop

if to_char(v_date,'d') in ('1','7') then

v_days:=v_days+1;

end if;

v_date:=v_date+1;

end loop;

return v_days;

END WORKEND;

------------------------第二种方法---------------------------------------------------------

create or replace

FUNCTION F_GETWORKDAY( BDATE_ IN DATE, EDATE_ IN DATE) RETURN NUMBER AS

/*************************************************************************************************************************

* 功能：

*    计算两个日期点之间的工作日的个数

*

* 参数：

*    BDATE: 开始日期

*    EDATE: 结束日期

*

* 返回值：

*    返回工作日的个数

*

* 作者&时间:

*    周继伟，2010年03月10日

*

* 修改：

*    第1次：[某人]于[时间]，[原因]

*    第2次：[某人]于[时间]，[原因]

*    第3次：[某人]于[时间]，[原因]

*    第4次：[某人]于[时间]，[原因]

*    第5次：[某人]于[时间]，[原因]

*

*************************************************************************************************************************/

TYPE holiday_type IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;

unworkdays holiday_type;/*申请非工作日内存表变量*/

TYPE holiday_record_type IS RECORD (sdate DATE,edate DATE);

holiday_record holiday_record_type;/*存储从表中取出设置好的单条节假日*/

CURSOR cur_holiday IS SELECT HOLIDAY_BDATE,HOLIDAY_EDATE from T_HOLIDAY;

datetmp DATE;

bWorkDay BOOLEAN DEFAULT TRUE;

i NUMBER DEFAULT 0;

iRtn NUMBER DEFAULT 0;

BEGIN

/*验证,如果开始日期或工作日数为空,则直接返回NULL*/

IF BDATE_ IS NULL OR EDATE_ IS NULL THEN RETURN 0; END IF;

/*将所有的非工作日存储在临时内存表中*/

OPEN cur_holiday;

IF cur_holiday%ISOPEN THEN

LOOP

<>

FETCH cur_holiday INTO holiday_record;

EXIT WHEN cur_holiday%NOTFOUND;

IF holiday_record.sdate IS NULL OR holiday_record.edate IS NULL THEN GOTO mycontinue1;  END IF;

IF holiday_record.sdate <= holiday_record.edate THEN

WHILE holiday_record.sdate <= holiday_record.edate

LOOP

unworkdays(i) := TO_CHAR(holiday_record.sdate,'MM-dd');

holiday_record.sdate := holiday_record.sdate + 1;

i := i + 1;

END LOOP;

END IF;

END LOOP;

END IF;

CLOSE cur_holiday;

datetmp := BDATE_;

WHILE datetmp <= EDATE_

LOOP

IF TO_CHAR(datetmp,'d') IN (1,7) THEN

bWorkDay := FALSE;

GOTO mycontinue2;

END IF;

IF bWorkDay THEN

IF unworkdays.LAST IS NOT NULL THEN

FOR j IN 0..unworkdays.LAST LOOP

IF TO_CHAR(datetmp,'MM-dd') = unworkdays(j) THEN

bWorkDay := FALSE;

GOTO mycontinue2;

END IF;

END LOOP;

ELSE

bWorkDay := FALSE;

END IF;

END IF;

<>

IF bWorkDay THEN iRtn := iRtn + 1; END IF;

datetmp := datetmp + 1;

bWorkDay := TRUE;

END LOOP;

RETURN iRtn;

END F_GETWORKDAY;

-----------------------------------------第三种方法------------------------------------------------

create or replace function te2(startdate in date, enddate in date) return integer is

rs integer;

counts integer; --两日期间的天数

yunum integer; --余数

weeknum integer; --星期几

yk integer; --余数中有几天周末

begin

select floor(enddate-startdate) into counts from dual; --不包括enddate这一天

counts:=counts+1; --//包括enddate这一天(加1)

rs:=counts;

yk:=0;

select mod(counts,7) into yunum from dual;

select to_number(to_char(startdate,'D')) into weeknum from dual;

if yunum>0 then

begin

if weeknum=6 then

begin

if yunum>1 then yk:=2;

else yk:=1;

end if;

end;

elsif weeknum=1 then                         --注意不是elseif

yk:=1;

else

begin

yk:=yunum-(7-weeknum);

if yk<0 then

yk:=0;

end if;

end;

end if;

end;

end if;

rs:=rs-floor(rs/7) *2-yk; --//注意integer 类型的rs/7

return rs;

end te2;

把if/elsif改成case:

case weeknum

when 6 then

begin

if yunum>1 then yk:=2;

else yk:=1;

end if;

end;

when 1 then                yk:=1;

else               begin

yk:=yunum-(7-weeknum);

if yk<0 then

yk:=0;

end if;

end;

end case;