Getting all the months in the current Fiscal Year with a Query on Oracle

logo_oracleI usually like to do everything dinamically, so I wanted to figure out the current set of months for the current fiscal year on Oracle DB using a simple query, from everyone’s favorite table dual.

So here’s there query:

SELECT TO_CHAR(add_months(TRUNC(SYSDATE, 'mm'), 
              DECODE(EXTRACT(MONTH FROM SYSDATE), 
              1,-7,
              2,-7,
              3,-7,
              4,-7,
              5,-7,
              6,5,
              5) - EXTRACT(MONTH FROM SYSDATE)+level), 
              'Mon-yy')
FROM dual
CONNECT BY level <= 12;

This will output the following (SYSDATE at the time of writing is 03/07/13 “mm/dd/yy”)

Jun-12
Jul-12
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Jan-13
Feb-13
Mar-13
Apr-13
May-13