How to get a list of months between 2 given dates using a query?
Gonna add this solution just because I think it's much cleaner than the others:
SELECT ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) date_out
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('28-Mar-2011', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
<= TRUNC(TO_DATE('29-Jun-2011', 'DD-MON-YYYY'), 'MON')
Something like this
SQL> ed
Wrote file afiedt.buf
select to_char( add_months( start_date, level-1 ), 'fmMonth' )
from (select date '2011-03-30' start_date,
date '2011-06-29' end_date
from dual)
connect by level <= months_between(
trunc(end_date,'MM'),
trunc(start_date,'MM') )
* + 1
SQL> /
TO_CHAR(ADD_MONTHS(START_DATE,LEVEL-
------------------------------------
March
April
May
June
should work.