previous month plsql

[eja2000]

can anyone give me the plsql for retrieving data that is for the previous month;
what i want is something like this;


WHERE data_month = previous month


thanks

 

[SantaMufasa]

Eja,

If, by "previous month" you mean "last month" (from today), then how about:

...WHERE data_month = to_char(trunc(sysdate,'mm')-1,'mm')

Let us know if that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 10:05 (04Jan05) UTC (aka "GMT" and "Zulu"),
@ 03:05 (04Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[eja2000]

will this work if the date is january 2005 (this month)?
i expect to see december 2004 data..
thanks

 

[SantaMufasa]

Eja,

As I usually say, "One test is worth 1000 expert opinions." If you ask me here in Tek-Tips, you'll still need to test it out with Oracle to prove that I'm right. So, my suggestion is to try this code and see if it works:

select to_char(trunc(sysdate,'mm')-1,'mm') from dual;

TO
--
12

1 row selected.

If you do not get the above results, then you are probably not using Oracle.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:35 (05Jan05) UTC (aka "GMT" and "Zulu"),
@ 08:35 (05Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[LKBrwnDBA]

In Oracle you could also use:

WHERE data_month = to_char(add_months(sysdate,-1),'MM')...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb