oracle语句怎么查工作日,SQL查询工作日 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区...

with t1 as (

/*连续的日期*/

SELECT to_date('2016-01-01', 'yyyy-mm-dd') + (level - 1) as normal_date

FROM dual CONNECT BY TRUNC(to_date('2016-01-01', 'yyyy-mm-dd')) + level - 1 <= TRUNC(to_date('2016-02-29','yyyy-mm-dd'))

),

t2 as (

select '2016-01-01' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-01-02' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-01-03' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-06' as special_date,'Y' as type,'补工作日' as note from dual

union

select '2016-02-07' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-08' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-09' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-10' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-11' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-12' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-13' as special_date,'N' as type,'法定节假日' as note from dual

union

select '2016-02-14' as special_date,'Y' as type,'补工作日' as note from dual

)

/*去除周末*/

select to_char(t1.normal_date,'yyyy-mm-dd') as normal_date from t1,t2 where TO_CHAR(t1.normal_date, 'd') NOT IN (1, 7) and t1.normal_date <> to_date(t2.special_date,'yyyy-mm-dd') and t2.type = 'N'

union

select t2.special_date as normal_date from t2 where t2.type = 'Y'