Oracle计算指定日期内的工作日(不包含周末)

~~~


1、获取当天是礼拜几:selectto_char(sysdate,'d')fromdual;--礼拜天为1,礼拜一为2,类推

2、获取 两个时间段间的 工作日:

select(trunc(&end_dt - &start_dt) -

((case

WHEN(8- to_number(to_char(&start_dt,'D'))) > trunc(&end_dt - &start_dt) +1THEN0

ELSE

trunc((trunc(&end_dt - &start_dt) -

(8- to_number(to_char(&start_dt,'D'))))/7) +1END) +

(case

WHENmod(8- to_char(&start_dt,'D'),7) > trunc(&end_dt - &start_dt) -1THEN0

ELSE

trunc((trunc(&end_dt - &start_dt) - (mod(8- to_char(&start_dt,'D'),7) +1))/7) +1END)))

asworkingdays

fromdual

下面的sql可以直接运行:

select(trunc(to_date('2010-11-11','yyyy-mm-dd') -to_date('2010-11-07','yyyy-mm-dd')) -

((case

WHEN(8- to_number(to_char(to_date('2010-11-07','yyyy-mm-dd'),'D'))) > trunc(to_date('2010-11-11','yyyy-mm-dd') -to_date('2010-11-07','yyyy-mm-dd')) +1THEN0

ELSE

trunc((trunc(to_date('2010-11-11','yyyy-mm-dd') -to_date('2010-11-07','yyyy-mm-dd')) -

(8- to_number(to_char(to_date('2010-11-07','yyyy-mm-dd'),'D'))))/7) +1END) +

(case

WHENmod(8- to_char(to_date('2010-11-07','yyyy-mm-dd'),'D'),7) > trunc(to_date('2010-11-11','yyyy-mm-dd') -to_date('2010-11-07','yyyy-mm-dd')) -1THEN0

ELSE

trunc((trunc(to_date('2010-11-11','yyyy-mm-dd') -to_date('2010-11-07','yyyy-mm-dd')) - (mod(8- to_char(to_date('2010-11-07','yyyy-mm-dd'),'D'),7) +1))/7) +1END)))

asworkingdays

fromdual

~~~

最后编辑于
© 著作权归作者所有,转载或内容合作请联系作者