--前提条件：DIM_oa_TIME 包含每一天，并且is_work=1 工作日 =0 非工作日 --详见：https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html create or replace function getworktime(begindate in date,enddate in date) return number as v_begindate date; v_enddate date; v_nextdate date;--下一天0时 v_date date;--当天0时 beforeworkhournum number(10,2);--开始时间育当天24点两时间相差几个小时 endworkhournum number(10,2);--结束时间与当天0点两时间相差几个小时 workhournum number(10,2);--两时间相差几个小时 workdaynum number(10,2);--两时间相差几个整天工作日 daynum number(10,2);--两时间相差几个整天 is_work int;--是否为非工作日 begin v_begindate :=begindate; v_enddate :=enddate; v_nextdate :=To_date(To_char(Trunc(v_begindate+1), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的下一天0时 v_date :=To_date(To_char(Trunc(v_enddate), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时 beforeworkhournum :=0; endworkhournum :=0; workhournum :=0; workdaynum :=0; daynum :=0; if v_begindate is null or v_enddate is null or v_begindate>v_enddate --开始日期大于结束日期时 then workhournum :=0; else if to_number(To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss')-To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss'))=0 --开始日期和结束日期时为同一天 then select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --这天是否节假日 if is_work=0 --or v_begindate is null or v_enddate is null then workhournum :=0; else workhournum := round(to_number(v_enddate-v_begindate)*24,2); end if; else select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --开始日期是否节假日 if is_work=0 --or v_begindate is null then beforeworkhournum :=0; else beforeworkhournum :=round(to_number(v_nextdate-v_begindate)*24,2)+beforeworkhournum;-- 开始时间到下一天0时的小时数 end if; select count(*) into workdaynum from DIM_oa_TIME where fdate>=v_begindate and fdate<=v_enddate and is_work=1; --工作日相差天数 select count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --结束日期是否节假日 if is_work=0 --or v_begindate is null then --如果v_enddate为节假日workdaynum不减1 endworkhournum :=0; else endworkhournum :=round(to_number(v_enddate-v_date)*24,2)+endworkhournum;-- 下一天0时到结束时间的小时数 workdaynum :=workdaynum-1;--如果v_enddate不为节假日workdaynum减1 end if; -- daynum :=to_date(To_char(v_enddate, 'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(To_char(v_begindate, 'yyyy/mm/dd'),'yyyy/mm/dd'); --工作日相差天数 end if; end if; workhournum:=workdaynum*24+endworkhournum+beforeworkhournum+workhournum; return workhournum; end; ---select getworktime(to_date('2021-04-01 11:12:38','yyyy-mm-dd hh24:mi:ss'),to_date('2021-04-03 11:12:38','yyyy-mm-dd hh24:mi:ss')) from dual; --select case when (count(*)-1)<0 then 0 else (count(*)-1) end from DIM_oa_TIME where fdate>=to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss') and fdate<=to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss') and is_work=1 --select (count(*)-2) from DIM_oa_TIME where fdate>=to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss') and fdate<=to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss') and is_work=1 --select count(*) into is_work from DIM_oa_TIME where fdate=to_date('2021-04-03 02:00:00','yyyy-mm-dd hh24:mi:ss') and is_work =1 -- select round(to_number(to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss'))*24,2) from dual -- select round(to_number(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24,2) from dual -- select to_number(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-16 00:00:00','yyyy-mm-dd hh24:mi:ss'))*24 from dual -- select round(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')-to_date('2020-01-15 15:23:58','yyyy-mm-dd hh24:mi:ss'),2) from dual --select To_date(To_char(Trunc(to_date('2020-01-16 17:57:48','yyyy-mm-dd hh24:mi:ss')), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') from dual