Thursday 27 June 2013

VALUE SET DYNAMIC-Query To create Dynamic List of year and months

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;

No comments:

Post a Comment