oracle 时间计算得到天数,ORACLE函数使用之计算2个时间段的工作日，返回天数-Oracle...

ORACLE函数使用之计算2个时间段的工作日，返回天数

create or replace function func_getWorkDayNum(fromdate date, --开始日期

todate date --截至日期

) return number is

Result number(4, 0);

v_tempfromdate date;

v_temptodate date;

v_tempfromdateVar varchar2(8);

v_temptodateVar varchar2(8);

v_NumHoliday number(4, 0);

begin

v_tempfromdate := fromdate;

v_temptodate := todate;

v_tempfromdateVar := to_char(v_tempfromdate, 'YYYYMMDD');

v_temptodateVar := to_char(v_temptodate, 'YYYYMMDD');

if v_tempfromdateVar > v_temptodateVar then

Result := 0;

else

--计算之间的工作日

v_NumHoliday := 0;

--计入第一天

select count(1)

into v_NumHoliday

from t_sys_holiday

where ISHOLIDAY = '0'

and to_char(to_date(year||'-'||month||'-'||day,'YYYY-MM-DD'),'YYYYMMDD') <= v_temptodateVar

and to_char(to_date(year||'-'||month||'-'||day,'YYYY-MM-DD'),'YYYYMMDD') >= v_tempfromdateVar;

Result := v_NumHoliday;

end if;

return(Result);

end func_getWorkDayNum;

附带依赖表结构，自己往里面插数据配置是否节假日标识t_sys_holiday

-- Create table

create table T_SYS_HOLIDAY

(

id NUMBER not null,

year VARCHAR2(4) not null,

month VARCHAR2(2) not null,

day NUMBER not null,

isholiday VARCHAR2(1) default 0 not null,

modifypersonid NUMBER,

modifytime DATE,

others VARCHAR2(100),

time DATE

)

tablespace FTXZZF_NEW

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 768K

next 1M

minextents 1

maxextents unlimited

);

-- Add comments to the columns

comment on column T_SYS_HOLIDAY.id

is 'ID主键';

comment on column T_SYS_HOLIDAY.year

is '年';

comment on column T_SYS_HOLIDAY.month

is '月';

comment on column T_SYS_HOLIDAY.day

is '日';

comment on column T_SYS_HOLIDAY.isholiday

is '是否节假日(1：节假日0：工作日)';

comment on column T_SYS_HOLIDAY.modifypersonid

is '修改人';

comment on column T_SYS_HOLIDAY.modifytime

is '修改时间';

comment on column T_SYS_HOLIDAY.others

is '其他';

comment on column T_SYS_HOLIDAY.time

is '时间';

-- Create/Recreate primary, unique and foreign key constraints

alter table T_SYS_HOLIDAY

add constraint PK_HOLIDAY_ID primary key (ID)

using index

tablespace FTXZZF_NEW

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 384K

next 1M

minextents 1

maxextents unlimited

);

alter table T_SYS_HOLIDAY

add constraint UK_HOLIDAY unique (YEAR, MONTH, DAY)

using index

tablespace FTXZZF_NEW

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 704K

next 1M

minextents 1

maxextents unlimited

);