该博客介绍了如何使用Oracle SQL进行日期的按年和按月拆分。提供了两个查询示例，第一个例子展示了如何将日期范围按完整年度进行拆分，第二个例子则演示了如何按月份进行详细拆分。每个拆分的区间都包含了起始和结束日期，对于按年拆分，输出了每年的开始和结束日期；对于按月拆分，输出了每个月的起始和结束日期。
Oracle日期按年拆分，日期按月拆分SQL
日期按年拆分
SELECT
(LEVEL - 1) as k
,extract(YEAR FROM start_date)+(LEVEL - 1) start_year
,extract(YEAR FROM end_date) end_year
,decode(extract(YEAR FROM start_date),extract(YEAR FROM start_date)+(LEVEL - 1),start_date,add_months(trunc(start_date, 'YYYY'), (LEVEL-1)*12)) start_date
,decode(extract(YEAR FROM end_date),extract(YEAR FROM start_date)+(LEVEL - 1),end_date,add_months(trunc(start_date, 'YYYY'), LEVEL*12) -1) end_date
FROM (SELECT to_date('2019-09-16','YYYY-MM-DD') AS end_date
,to_date('2018-04-11','YYYY-MM-DD') AS start_date
FROM dual)
CONNECT BY LEVEL <= (extract(YEAR FROM end_date) - extract(YEAR FROM start_date)) + 1;
输出结果
| K | START_YEAR | END_YEAR | START_DATE | END_DATE | |
| 1 | 0 | 2018 | 2019 | 2018/4/11 | 2018/12/31 |
| 2 | 1 | 2019 | 2019 | 2019/1/1 | 2019/9/16 |
日期按月拆分
SELECT decode(to_char(trunc(start_date_m
,'MM')
,'YYYY-MM')
,to_char(trunc(add_months(start_date_m
,(LEVEL - 1))
,'MM')
,'YYYY-MM')
,start_date_m
,trunc(add_months(start_date_m
,(LEVEL - 1))
,'MM')) new_start_date
,decode(to_char(trunc(end_date_m
,'MM')
,'YYYY-MM')
,to_char(trunc(add_months(start_date_m
,(LEVEL - 1))
,'MM')
,'YYYY-MM')
,end_date_m
,last_day(add_months(start_date_m
,(LEVEL - 1)))) new_end_date
,to_char(trunc(add_months(start_date_m
,(LEVEL - 1))
,'MM')
,'YYYY-MM') year_month_x1
,start_date_m
,end_date_m
FROM ((SELECT to_date('2018-12-31'
,'YYYY-MM-DD') AS end_date_m
,to_date('2018-04-11'
,'YYYY-MM-DD') AS start_date_m
FROM dual))
CONNECT BY LEVEL <= (extract(MONTH FROM end_date_m) - extract(MONTH FROM start_date_m)) + 1;
输出结果
| NEW_START_DATE | NEW_END_DATE | YEAR_MONTH_X1 | START_DATE_M | END_DATE_M | |
| 1 | 2018/4/11 | 2018/4/30 | 2018-04 | 2018/4/11 | 2018/12/31 |
| 2 | 2018/5/1 | 2018/5/31 | 2018-05 | 2018/4/11 | 2018/12/31 |
| 3 | 2018/6/1 | 2018/6/30 | 2018-06 | 2018/4/11 | 2018/12/31 |
| 4 | 2018/7/1 | 2018/7/31 | 2018-07 | 2018/4/11 | 2018/12/31 |
| 5 | 2018/8/1 | 2018/8/31 | 2018-08 | 2018/4/11 | 2018/12/31 |
| 6 | 2018/9/1 | 2018/9/30 | 2018-09 | 2018/4/11 | 2018/12/31 |
| 7 | 2018/10/1 | 2018/10/31 | 2018-10 | 2018/4/11 | 2018/12/31 |
| 8 | 2018/11/1 | 2018/11/30 | 2018-11 | 2018/4/11 | 2018/12/31 |
| 9 | 2018/12/1 | 2018/12/31 | 2018-12 | 2018/4/11 | 2018/12/31 |
--刘轶鹤
855
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
