Ask Question
Help Others
Home › SQL & PL/SQL

List Year Month for Current and Previous Month

Fri, 07/11/2008 - 11:13am — Lepa
Your rating: None Average: 5 (1 vote)

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.

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
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!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
1 + 3 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Unanswered Forum Posts

Title Under Posted on
How to run nvision report through portal PeopleSoft Technical 08/31/2010 - 4:45am
Any1 here who can help? PeopleSoft Technical 08/31/2010 - 2:12am
How to create matrix layout PeopleSoft Technical 08/27/2010 - 2:22am
UnCaught C++ Exception PeopleSoft Technical 08/10/2010 - 9:05am
more