Oracle SQL Query to list Months and Years

Shailender Thallam

13 years ago

Here is the Query to list Months and Years. I developed this query to prepare a LOV for Credit Card Expiration date.

/**************************************************************************
 *PURPOSE: To list out Months and Years                                   *
 *PARAMETERS: LEVEL count                                                 *
 *AUTHOR: Shailender Thallam                                              *
 **************************************************************************/
SELECT a.months
  ||'/'
  ||b.years credit_card_expiration_date
FROM
  (SELECT TO_CHAR(add_months(TRUNC(SYSDATE, 'YYYY'), LEVEL - 1), 'MM') months
  FROM dual
    CONNECT BY LEVEL <= 12
  ) a,
  (SELECT SUBSTR(EXTRACT(YEAR FROM SYSDATE) + (LEVEL-1),3) years
  FROM dual
    CONNECT BY LEVEL <=10
  ) b
WHERE to_date(a.months
  ||'/'
  ||b.years,'mm/yy')>=SYSDATE
ORDER BY b.years,
  a.months;

The above query lists months and years down the line of 10 years.

I have developed this query using CONNECT BY LEVEL concept. If you want more information of this concept please read this article

For Further Reading