OraFAQ Forum: Reports & Discoverer » add or subtract numbers with date

Home » Developer & Programmer » Reports & Discoverer » add or subtract numbers with date

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

add or subtract numbers with date [message #263235]

Wed, 29 August 2007 08:25

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

Report message to a moderator

Re: add or subtract numbers with date [message #263256 is a reply to message #263235]

Wed, 29 August 2007 09:08

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

Re: add or subtract numbers with date [message #263347 is a reply to message #263256]

Wed, 29 August 2007 15:26

Littlefoot
Messages: 21813
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.

Report message to a moderator

Re: add or subtract numbers with date [message #263472 is a reply to message #263347]

Thu, 30 August 2007 02:29

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

I didn't forget. But become lazy and pasted a snippet.
Point noted. Next time I'll post/paste it fully.

By
Vamsi

Report message to a moderator

Re: add or subtract numbers with date [message #263626 is a reply to message #263235]

Thu, 30 August 2007 09:30

brintha
Messages: 28
Registered: August 2007

Junior Member

littlefoot & vamsi,
Thanks for your reply.
regards,
Brintha.r

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 12:15:14 CDT 2024