Oracle: Get Month Start and End Dates for a Given Period (i.e. Year)

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.

Google+ Comments

Comments are closed.