Home » SQL & PL/SQL » SQL & PL/SQL » date query (9.2.0.6)
date query [message #309481] Thu, 27 March 2008 12:33 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
Currently we are in the month of Mar-08. What will be the query to display the value of 3 months back?

For example, to display the current month, I am doing this:

SQL>SELECT TO_CHAR(SYSDATE, 'MON-YY') FROM DUAL;

MAR-08

To display 3 months back, I know i can subtract 90 days from SYSDATE and display, like this:

SQL>SELECT TO_CHAR(SYSDATE-90, 'MON-YY') FROM DUAL;

DEC-07

But do we have any alternative wherein we can subtract the number of months (3 months in this case) and get the same output. I don't want to subtract 90 days as in the above case.

something like this:

SQL>SELECT TO_CHAR(SYSDATE, 'MON-YY')-3 FROM DUAL;
-- This statement returns error now


Regards,
Sandi
Re: date query [message #309483 is a reply to message #309481] Thu, 27 March 2008 12:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use the built-in function ADD_MONTHS

[Updated on: Thu, 27 March 2008 12:44]

Report message to a moderator

Re: date query [message #309485 is a reply to message #309481] Thu, 27 March 2008 12:45 Go to previous messageGo to next message
SueC
Messages: 4
Registered: March 2008
Location: Baltimore, MD
Junior Member
Sandi, does this do it?



SQL> SELECT TO_CHAR((SYSDATE - 90), 'MON-YY') FROM DUAL;

TO_CHAR((SYSDATE-90),'MON-YY')
-------------------------------------------------------------
DEC-07


Sue

Re: date query [message #309489 is a reply to message #309485] Thu, 27 March 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT 3 months back.
See Frank's answer.

Regards
Michel
Re: date query [message #309494 is a reply to message #309485] Thu, 27 March 2008 12:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SueC wrote on Thu, 27 March 2008 18:45
Sandi, does this do it?



SQL> SELECT TO_CHAR((SYSDATE - 90), 'MON-YY') FROM DUAL;

TO_CHAR((SYSDATE-90),'MON-YY')
-------------------------------------------------------------
DEC-07


Sue



Did you actually read the question??
Re: date query [message #309510 is a reply to message #309481] Thu, 27 March 2008 14:17 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Yes I agree with Frank.

Use the built-in Function add_months()

For example add_months(sysdate, -3) will return the date exactly 3 months back. If you just want the format 'Mon-YY' to be as output use the to_char function:

eg:-

select To_char(add_months(sysdate, -3), 'Mon-YY') from Dual;

Hope this helps....
Re: date query [message #309565 is a reply to message #309481] Thu, 27 March 2008 22:45 Go to previous message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank you all. I feel ADD_MONTHS can be used in this situation.


Regards,
Sandi
Previous Topic: Copy Table Structure and Data
Next Topic: Merging Tables through SQL
Goto Forum:
  


Current Time: Sun Dec 11 08:15:10 CST 2016

Total time taken to generate the page: 0.04172 seconds