A little off topic but hopefully still useful. We often have a requirement for report filters based on a Date range, Calendar Month, Periodic etc. so it’s nice to have a dynamic query to generate the results for us as we use them in LOV’s and report link’s. So here’s a few examples which you may find useful….

By Month(s) (descending back 10 years):

SELECT initcap(to_char(add_months(sysdate, -(LEVEL - 1)),'MONTH YYYY'))               month_year
,      initcap(to_char(trunc(add_months(sysdate, -(LEVEL - 1)),'MM'),'DD-MON-YYYY'))  start_date
,      initcap(to_char(last_day(add_months(sysdate, -(LEVEL - 1))),'DD-MON-YYYY'))    end_date
FROM DUAL
CONNECT BY add_months(sysdate, LEVEL) <= add_months(sysdate, 120)

Which produces…..

November  2009	01-Nov-2009	30-Nov-2009
October   2009	01-Oct-2009	31-Oct-2009
September 2009	01-Sep-2009	30-Sep-2009
August    2009	01-Aug-2009	31-Aug-2009
July      2009	01-Jul-2009	31-Jul-2009
June      2009	01-Jun-2009	30-Jun-2009
May       2009	01-May-2009	31-May-2009
April     2009	01-Apr-2009	30-Apr-2009
March     2009	01-Mar-2009	31-Mar-2009
February  2009	01-Feb-2009	28-Feb-2009
January   2009	01-Jan-2009	31-Jan-2009
December  2008	01-Dec-2008	31-Dec-2008
November  2008	01-Nov-2008	30-Nov-2008
October   2008	01-Oct-2008	31-Oct-2008
September 2008	01-Sep-2008	30-Sep-2008
August    2008	01-Aug-2008	31-Aug-2008
July      2008	01-Jul-2008	31-Jul-2008
June      2008	01-Jun-2008	30-Jun-2008
May       2008	01-May-2008	31-May-2008
April     2008	01-Apr-2008	30-Apr-2008
March     2008	01-Mar-2008	31-Mar-2008
February  2008	01-Feb-2008	29-Feb-2008
January   2008	01-Jan-2008	31-Jan-2008
December  2007	01-Dec-2007	31-Dec-2007
November  2007	01-Nov-2007	30-Nov-2007
October   2007	01-Oct-2007	31-Oct-2007
September 2007	01-Sep-2007	30-Sep-2007
August    2007	01-Aug-2007	31-Aug-2007
July      2007	01-Jul-2007	31-Jul-2007
June      2007	01-Jun-2007	30-Jun-2007

By Months and Years:

SELECT to_char(sysdate, 'DD-MON-YYYY')                           start_date
,      to_char(add_months(sysdate, -((LEVEL)*3)),'DD-MON-YYYY') end_date
,      decode(level*3,3,'Last 3 Months',6,'Last 6 Months',9,'Last 9 Months',12,'Last 12 Months') month
,      level * -1                               lvl
FROM   DUAL
CONNECT BY add_months(sysdate, ((LEVEL-1)*3)) <= add_months(sysdate, 9)
UNION
SELECT to_char(trunc(add_months(sysdate, -((LEVEL - 1)*12)),'YYYY'),'DD-MON-YYYY')            start_date
,      to_char(trunc(last_day(add_months(sysdate, -(((LEVEL -2 )*12)+11)))),'DD-MON-YYYY')    end_date
,      initcap(to_char(add_months(sysdate, -((LEVEL - 1)*12)),'YYYY')) year
,      level * -20 lvl
FROM   DUAL
CONNECT BY add_months(sysdate, ((LEVEL-1)*12)) <= add_months(sysdate, 120)
order by 4 desc

Which produces…..

05-NOV-2009	05-AUG-2009	Last 3 Months
05-NOV-2009	05-MAY-2009	Last 6 Months
05-NOV-2009	05-FEB-2009	Last 9 Months
05-NOV-2009	05-NOV-2008	Last 12 Months
01-JAN-2009	31-DEC-2009	2009
01-JAN-2008	31-DEC-2008	2008
01-JAN-2007	31-DEC-2007	2007
01-JAN-2006	31-DEC-2006	2006
01-JAN-2005	31-DEC-2005	2005
01-JAN-2004	31-DEC-2004	2004
01-JAN-2003	31-DEC-2003	2003
01-JAN-2002	31-DEC-2002	2002
01-JAN-2001	31-DEC-2001	2001
01-JAN-2000	31-DEC-2000	2000
01-JAN-1999	31-DEC-1999	1999

And here’s how it looks in 2 report regions as links: