oracle 查出某月周末,oracle查询某月第某周 这一周的开始日期和结束日期

满意答案

音乐书海102

2017.12.14

采纳率：56%    等级：9

已帮助：315人

with t1 as (select level id, '201507' || lpad(level, 2, '0') col from dual connect by level < 32),t2 as (select 0 id, null, to_char(trunc(to_date(col, 'yyyymmdd'), 'month'), 'yyyymmdd') end_dt from t1 where rownum = 1 union all select 32, null, '-' || to_char(last_day(trunc(to_date(col, 'yyyymmdd'), 'month')), 'yyyymmdd') from t1 where rownum = 1 union all select id, to_date(col, 'yyyymmdd'), decode(to_number(to_char(to_date(col, 'yyyymmdd'), 'D')) - 1, 0, '-' || col, 1, col) from t1 where col <= to_char(last_day(trunc(to_date(substr(col, 1, 6) || '01', 'yyyymmdd'), 'month')), 'yyyymmdd')),t3 as (select id, decode(instr(lag(end_dt, 1) over(order by id), '-'), 1, null, lag(end_dt, 1) over(order by id)) start_dt, end_dt from t2 where end_dt is not null order by id)select '第' || rownum || '周:' des, start_dt || end_dt from t3 where start_dt is not null order by id;

00分享举报