Oracle Apps Knowledge Sharing

Query to get list of month and year since specific date in Oracle (Hierarchical queries - CONNECT BY Example)

        In this post, we have given a query which will help you to get the list of months with year information since specific date.

For example, below query will get months and year in format of JAN-2019, FEB-2019 till today.

 Query: 

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,((LEVEL-1)*-1)),'MON-RRRR') mon_yyyy
  FROM dual
 CONNECT BY ADD_MONTHS(SYSDATE,((LEVEL-1)*-1)) > TO_DATE('01-01-2019','MM-DD-RRRR') 
 ORDER BY LEVEL;

Sample Run: