Home » SQL & PL/SQL » SQL & PL/SQL » date format not working
date format not working [message #313000] Thu, 10 April 2008 05:46 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am trying to run the below select statement but its giving error

SELECT MAX(TO_CHAR(date_reading,'DD/MM/YYYY HH24:MI:SS'))
  FROM t_read
 WHERE date_reading <= TO_DATE('Dec-02-2007 00:15:00','DD/MM/YYYY HH24:MI:SS')

The error is :-
TO_DATE('Dec-02-2007 00:15:00','DD/MM/YYYY HH24:MI:SS')
        *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


Please let me know as where I am going wrong.

Thanks,
Mona
Re: date format not working [message #313003 is a reply to message #313000] Thu, 10 April 2008 05:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

date_reading <= TO_DATE('Dec-02-2007 00:15:00','DD/MM/YYYY HH24:MI:SS')

Read it again. You will understand it.

Regards

Raj
Re: date format not working [message #313004 is a reply to message #313003] Thu, 10 April 2008 05:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Skirting the edge of spoon feeding here....

Compare the format you've got the date written in to the format mask that you've specified in the TO_DATE statement.
Re: date format not working [message #313019 is a reply to message #313004] Thu, 10 April 2008 06:56 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Actually the date data I am getting is in format 'Dec-02-2007 00:15:00' only and I have to get one in format 'DD/MM/YYYY HH24:MI:SS'.
What to do in this case.


Re: date format not working [message #313020 is a reply to message #313019] Thu, 10 April 2008 06:59 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Convert from string to date using your input format then convert from date to string using your output format.

Regards
Michel

[Updated on: Thu, 10 April 2008 06:59]

Report message to a moderator

Previous Topic: ORACLE QUERY
Next Topic: Help needed on an Analytical Query
Goto Forum:
  


Current Time: Sun Dec 04 14:33:13 CST 2016

Total time taken to generate the page: 0.06538 seconds