Home » Developer & Programmer » Reports & Discoverer » add or subtract numbers with date
add or subtract numbers with date [message #263235] Wed, 29 August 2007 08:25 Go to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
hi,
I need to subtract a number from date to get the date before that number of days.

S.START_TIME BETWEEN 'FEB-16-07' AND TO_DATE ('22-FEB-2007')+ 2
I got error like 
ORA-01858: a non-numeric character was found where a numeric was expected 

please help me out with the CORRECT code.


regards,
brintha.r
Re: add or subtract numbers with date [message #263256 is a reply to message #263235] Wed, 29 August 2007 09:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Always it is better to use explicit conversion.
Quote:
'FEB-16-07' AND TO_DATE ('22-FEB-2007')
Try to use to_date with date format like
TO_DATE('FEB-16-07','MON-DD-RR')
AND TO_DATE ('22-FEB-2007','DD-MON-YYYY')
By
Vamsi
Re: add or subtract numbers with date [message #263347 is a reply to message #263256] Wed, 29 August 2007 15:26 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Heh, Vamsi, you forgot to add 2 days!
... s.start_time BETWEEN to_date('FEB-16-07', 'mon-dd-yy')
                     AND to_date('22-FEB-2007', 'dd-mon-yyyy')+ 2

By the way, is there any special reason for you to mix date formats from MON-DD-YY to DD-MON-YYYY? Not that it won't work, but - I'd rather have it uniform.
Re: add or subtract numbers with date [message #263472 is a reply to message #263347] Thu, 30 August 2007 02:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Razz I didn't forget. But become lazy and pasted a snippet.
Point noted. Next time I'll post/paste it fully.

By
Vamsi
Re: add or subtract numbers with date [message #263626 is a reply to message #263235] Thu, 30 August 2007 09:30 Go to previous message
brintha
Messages: 28
Registered: August 2007
Junior Member
littlefoot & vamsi,
Thanks for your reply.
regards,
Brintha.r
Previous Topic: Message box interprets while sending mail from FORMS
Next Topic: DISPLAY PROBLEM IN PDF OR HTML FORMAT BUT NOT IN THE REPORT BUILDER PREVIEWER
Goto Forum:
  


Current Time: Thu Dec 08 06:40:03 CST 2016

Total time taken to generate the page: 0.13607 seconds