Oracle SQL Query to list Months and Years

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

/**************************************************************************
 *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

  1. SQL Query to list Active Responsibilities of a Active User
  2. SQL Query to list out Responsibilities assigned to a Request Group
  3. SQL Query to list all Oracle Reports assigned to a Responsibility
OracleAppsDNA.com