时间：2021-07-01 10:21:17 帮助过：4人阅读
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;oracle根据月份获取每周的始末日期
标签：