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 |
--刘轶鹤