Oracle - Select a list of months for a Date Range
Case 1 – the 13 months between '10/18/2007' & '11/17/2008'
select add_months (trunc (to_date('10/18/2007','MM/DD/YYYY'), 'MM'), 1*Level -1)
Month FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(to_date('11/17/2008','MM/DD/YYYY'), to_date('10/18/2007','MM/DD/YYYY')) + 1
order by month
MONTH
----------------------
1-Oct-2007
1-Nov-2007
1-Dec-2007
1-Jan-2008
1-Feb-2008
1-Mar-2008
1-Apr-2008
1-May-2008
1-Jun-2008
1-Jul-2008
1-Aug-2008
1-Sep-2008
1-Oct-2008
Case 2 – the 1 month shared between identical dates
select add_months (trunc (to_date('10/20/2007','MM/DD/YYYY'), 'MM'), 1*Level -1)
Month FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(to_date('10/20/2007','MM/DD/YYYY'), to_date('10/20/2007','MM/DD/YYYY')) + 1
order by month
MONTH
----------------------
1-Oct-2007