List Year Month for Current and Previous Month

Your rating: None Average: 3 (2 votes)

The query will come handy if you would like to pull transactions that have taken place in the current month as well as the previous one.

SELECT TO_CHAR (SYSDATE, 'YYYY'), TO_CHAR (SYSDATE, 'MM')
  FROM DUAL
UNION ALL
SELECT TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYY'),
       TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'MM')
  FROM DUAL;

TO_C TO
---- --
2008 07
2008 06

2 rows selected.

What if the current system date is '01/15/2008', would the above SQL pull last year 2007 and month 12? Well. let do a quick test.

SELECT TO_CHAR (TO_DATE ('01/15/2008', 'MM/DD/YYYY'), 'YYYY'), TO_CHAR (TO_DATE ('01/15/2008', 'MM/DD/YYYY'), 'MM')
  FROM DUAL
UNION ALL
SELECT TO_CHAR (ADD_MONTHS (TO_DATE ('01/15/2008', 'MM/DD/YYYY'), -1), 'YYYY'),
       TO_CHAR (ADD_MONTHS (TO_DATE ('01/15/2008', 'MM/DD/YYYY'), -1), 'MM')
  FROM DUAL;

TO_C TO
---- --
2008 01
2007 12

2 rows selected.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!