求一个sql语句，返回两个日期参数之间的连续年月

Question

如题，请各位大神帮帮忙！

Answer 1

求两个日期之前的相隔月份。
select months_between(date1,date2) from dual;

Answer 2

create table t1 (b_date date,e_date date);insert into t1 values (date'2011-06-01',date'2011-12-01');
insert into t1 values (date'2012-03-01',date'2012-06-01');
commit;select distinct to_char(add_months(b_date,level-1),'yyyy-mm') d
from t1
connect by level< trunc(months_between(e_date,b_date),0)+2
order by d
       D
-------------------------
1 2011-06
2 2011-07
3 2011-08
4 2011-09
5 2011-10
6 2011-11
7 2011-12
8 2012-03
9 2012-04
10 2012-05
11 2012-06

Answer 3

--取指定时间段内每一天
SELECT to_date('2011-10-10' , 'YYYY-MM-DD') + ROWNUM - 1 service_date FROM dual 
  CONNECT BY ROWNUM <= (to_date('2011-10-28' , 'YYYY-MM-DD') - to_date('2011-10-10' , 'YYYY-MM-DD') + 1);
  
SERVICE_DATE              
------------------------- 
2011-10-10 00:00:00       
2011-10-11 00:00:00       
2011-10-12 00:00:00       
2011-10-13 00:00:00       
2011-10-14 00:00:00       
2011-10-15 00:00:00       
2011-10-16 00:00:00       
2011-10-17 00:00:00       
2011-10-18 00:00:00       
2011-10-19 00:00:00       
2011-10-20 00:00:00       
2011-10-21 00:00:00       
2011-10-22 00:00:00       
2011-10-23 00:00:00       
2011-10-24 00:00:00       
2011-10-25 00:00:00       
2011-10-26 00:00:00       
2011-10-27 00:00:00       
2011-10-28 00:00:00  

Answer 4

改个函数 trunc → round
select distinct to_char(add_months(b_date,level-1),'yyyy-mm') d
from t1
connect by level< round(months_between(e_date,b_date),0)+2
order by d