oracle日期相减工作日_Oracle 计算两个日期间时间排除非工作日及非工作时间精确至分钟...

create or replace functionwk_hours_between(

i_startTimevarchar2, --起始时间：( 格式：'YYYY-MM-DD HH24:MI:SS' )

i_endTime varchar2 --结束时间：( 格式：'YYYY-MM-DD HH24:MI:SS' )

)return number

isv_real_startTime date;--开始时间 变量

v_real_endTime date;--结束时间 变量

v_hours number(18,0);--计算结果

v_number number(18,4);begin

--格式转换

v_real_startTime := to_date(i_startTime,'YYYY-MM-DD HH24:MI:SS');

v_real_endTime := to_date(i_endTime,'YYYY-MM-DD HH24:MI:SS');--开始时间及结束时间转换

if v_real_startTime > v_real_endTime --如果起始时间大于结束时间，将起始时间与结束时间替换

then

select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime fromdual;end if;if v_real_startTime

thenv_real_startTime:=trunc(v_real_startTime,'dd')+9/24;/*-- 如果大于当天12点，且小于当天14点，将其置为当天14点(因为你下午是14点上班)

elsif v_real_startTime>trunc(v_real_startTime,'dd')+12/24 and v_real_startTime

then

v_real_startTime:=trunc(v_real_startTime,'dd')+14/24;*/

--如果大于当天18点，将其置为第二天9点

elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24

thenv_real_startTime:=trunc(v_real_startTime+1,'dd')+9/24;end if;--如果小于当天9点，将其置为昨天18点

if v_real_endTime

thenv_real_endTime:=trunc(v_real_endTime-1,'dd')+18/24;/*-- 如果大于当天12点，且小于当天14点，将其置为当天12点(因为你上午是12点下班)

elsif v_real_endTime>trunc(v_real_endTime,'dd')+12/24 and v_real_endTime

then

v_real_endTime:=trunc(v_real_endTime,'dd')+12/24;*/elsif v_real_endTime>trunc(v_real_endTime,'dd')+18/24 --如果大于当天18点，将其置为当天18天(因为你是18点下班)

thenv_real_endTime:=trunc(v_real_endTime,'dd')+18/24;end if;--with tem as 语句 将后面语句查询结果赋值于tem 使用于多次查询的语句，可简化

with a as( select v_real_startTime+(level-1)/24 ascdate,

trunc(v_real_startTime+(level-1)/24,'hh') astr_cdatefromdual

connectby level <= (v_real_endTime-v_real_startTime)*24+2 ),--connect by level 递归查询 按小时对时间进行拆分

--对a中内容进行筛选，排除非工作日的 to_char 'D' 得到所在所在周内的第几天

b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select hdate from holidays) and to_char(a.cdate,'D') not in ('1','7') ), --排除周六、日 和 节假日

--对b中时间点进行转换 主要以工作时间09——18来进行

c as( select (case when to_char(t1.cdate,'hh24') in ('18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1, --超过18点的按18点

(case when to_char(t1.cdate2,'hh24') in ('09') then trunc(t2.cdate+1/24,'hh24') else t2.cdate end) as cdate2--超过9点的按9点

fromb t1left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24')order by (case when to_char(t1.cdate,'hh') in ('09','18') then trunc(t1.cdate,'hh') else t1.cdate end) ),--上一步将超过9点和18点的转换成了整点，此步将整点的字段值信息进行调整

--对c表中信息进行筛选，选取在工作时间点上的来计算

d as ( select (case when c.cdate1>v_real_endTime thenv_real_endTimeelse c.cdate1 end) ascdate1,

c.cdate2fromcwhere to_char(c.cdate1,'hh24') in ('10','11','12','13','14','15','16','17','18') )--*1440为时间进度计算到分钟

select nvl(sum(d.cdate1-nvl(d.cdate2,d.cdate1))*1440,0) into v_number fromd;returnv_number;end;