Took me a few minutes to think this one through, so I thought I’d share. In this example I show the start and end date for each month in the period that contains the fiscal year from 7/1/10 to 6/30/11.
select trunc(add_months('01-JUL-10', level-1), 'MM') start_date,
last_day(add_months('01-JUL-10', level-1)) end_date
from dual connect by level <= ceil(months_between('30-JUN-11', '01-JUL-10'))
| START_DATE | END_DATE |
|---|---|
| 7/1/2010 | 7/31/2010 |
| 8/1/2010 | 8/31/2010 |
| 9/1/2010 | 9/30/2010 |
| 10/1/2010 | 10/31/2010 |
| 11/1/2010 | 11/30/2010 |
| 12/1/2010 | 12/31/2010 |
| 1/1/2011 | 1/31/2011 |
| 2/1/2011 | 2/28/2011 |
| 3/1/2011 | 3/31/2011 |
| 4/1/2011 | 4/30/2011 |
| 5/1/2011 | 5/31/2011 |
| 6/1/2011 | 6/30/2011 |
It should work for periods of any length (i.e. one quarter, 6 months, 2 years, etc) by replacing the period start and end dates.