Oracle SQL Query to list Months and Years

By | September 25, 2011

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

WOW! Did you like this post? We'll send more interesting posts like Oracle SQL Query to list Months and Years to you!
Enter your Email Address:
  • Mildarow

    or 
    select to_char(add_months(sysdate, level),’mm/yy’) from dual connect by level <= 120

    • @@82370c395474a8eceecd9b166f55ac40:disqus  Thats great! Thanks for Sharing 🙂