Home » SQL & PL/SQL » SQL & PL/SQL » how to extract the same date when it is last day of the month? (oracle 9i)
how to extract the same date when it is last day of the month? [message #340056] Mon, 11 August 2008 04:56 Go to next message
kmkan28
Messages: 14
Registered: December 2005
Junior Member
hi,
i have a query which will bring the same date of the next month.
but when i enter the following query

select to_char(add_months('30-APR-08',1),'DD/MON/YYYY') from dual

the result will be

31-MAY-08.

Instead of the above the result i want the exact date of the next month to be returned

30-MAY-08.

Likely when i enter the FOLLWING SQL STATEMENT

select to_char(add_months('29-FEB-08',1),'DD/MON/YYYY') from dual

IT RESULTS IN 31/MAR-2008. INSTEAD OF 29-MAR-08.

CAN ANY ONE HELP ME TO RESOLVE THE ISSUE?

THANKS IN ADVANCE


kani
Re: how to extract the same date when it is last day of the month? [message #340057 is a reply to message #340056] Mon, 11 August 2008 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle is smart enough to see that your date in the last day of the month and so gives you the last day of the next month when you add 1 month.

If you don't want this then build your date adding one month and then pasting the day you want.

By the way what would be the date for "31-MAY-08"?

Regards
Michel
Re: how to extract the same date when it is last day of the month? [message #340058 is a reply to message #340056] Mon, 11 August 2008 05:04 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use some TO_CHAR.Concatenation || operator.



Quote:

By the way what would be the date for "31-MAY-08"?

Laughing

Good One Michel.

Regards,
Rajat

[Updated on: Mon, 11 August 2008 05:07]

Report message to a moderator

Re: how to extract the same date when it is last day of the month? [message #340059 is a reply to message #340057] Mon, 11 August 2008 05:08 Go to previous messageGo to next message
kmkan28
Messages: 14
Registered: December 2005
Junior Member
in that case of 31-may-2008 then the result would be 30-jun-08.it is fair.But if the corresponding date is there it has to bring the same date like 30-jun-08 will have to bring the 30-JUL-08.
Re: how to extract the same date when it is last day of the month? [message #340098 is a reply to message #340056] Mon, 11 August 2008 08:05 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
kmkan28 wrote on Mon, 11 August 2008 05:56


select to_char(add_months('30-APR-08',1),'DD/MON/YYYY') from dual



I haven't had to do one of these in a long time, but '30-APR-08' is a string and not a date:
FOO SCOTT>select add_months('30-APR-08',1) from dual;
select add_months('30-APR-08',1) from dual
                  *
ERROR at line 1:
ORA-01843: not a valid month


FOO SCOTT>

TO_DATE is a function in Oracle. Please use it.
Previous Topic: printing special character
Next Topic: HELP UNDERSTANDING CODE
Goto Forum:
  


Current Time: Fri Dec 09 17:35:09 CST 2016

Total time taken to generate the page: 0.41960 seconds