给定一个月: 2016-06
星期一为一周的第一天, 这个月的日期周期内按周分组, 得到每一周的日期区间。
如下 : 2016-06-01 2016-06-05
2016-06-06 2016-06-12
2016-06-13 2016-06-19
2016-06-20 2016-06-26
2016-06-27 2016-06-30
select min(dt) monday,
max(dt) sunday
from
(select level rn,
trunc(to_date('2016-6', 'YYYY-MM'), 'mm') + level - 1 dt,
max(decode(to_char(trunc(to_date('2016-6', 'YYYY-MM'), 'mm') + level - 1, 'd'), 2, level, 0)) over(order by level) flag
from dual
connect by level <= last_day(trunc(to_date('2016-6', 'YYYY-MM'))) - trunc(to_date('2016-6', 'YYYY-MM'), 'mm') + 1)
group by flag
order by min(rn);