Home » SQL & PL/SQL » SQL & PL/SQL » Getting error
Getting error [message #195097] Wed, 27 September 2006 01:41 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi Friends,
I am getting error while giving this

SELECT TO_DATE ('20060927 00', 'ddmmyyyy hh')
FROM DUAL

Regards
Marlon
Re: Getting error [message #195101 is a reply to message #195097] Wed, 27 September 2006 01:51 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Your format mask does not match your data.
Re: Getting error [message #195104 is a reply to message #195101] Wed, 27 September 2006 01:56 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Then how to give becos i am passing this variable to a procedure
which gives error when the hour is 00.

regards
Marlon
Re: Getting error [message #195107 is a reply to message #195097] Wed, 27 September 2006 01:59 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

SELECT TO_DATE ('20060927 00', 'ddmmyyyy hh')
FROM DUAL
There is no such hour 0 in 12 hour format.


Either use this
SELECT to_date ('20060927 00', 'ddmmyyyy hh24')
FROM DUAL;
or this
SELECT to_date ('20060927 01', 'ddmmyyyy hh') -- dont use 00 hour in 12 hour format
FROM DUAL;




Sandy
Re: Getting error [message #195108 is a reply to message #195107] Wed, 27 September 2006 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
20060927 is NOT ddmmyyyy!
Re: Getting error [message #195110 is a reply to message #195104] Wed, 27 September 2006 02:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The other problem (that Todd alluded to quite succinctly) apart from the Hour part of the format mask being wrong is that the ENTIRE FORMAT MASK doesn't match the data.

Your date string is '20060927' which is presumably in a yyyymmdd and meant to be the 27th of September, 2006
The Mask you use is ddmmyyyy, and the result you get is 9th Jun, 927, which is round about the time that King Athelstan consolidated his rule over the territory of the Anglo-Saxons, but probably not the date you're looking for.

You need to use:
SELECT TO_DATE ('20060927 00', 'yyyymmdd hh24')
FROM DUAL


[Drat - beaten to it by Frank. Curse these Admins!]

[Updated on: Wed, 27 September 2006 05:33]

Report message to a moderator

Re: Getting error [message #195176 is a reply to message #195110] Wed, 27 September 2006 05:31 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
this will solve your problem
SELECT TO_DATE ('20060927 00', 'ddmmyyyy hh24')
FROM DUAL
regards
Muhammad Shahid Mughal
Faisalabad Pakistan
Re: Getting error [message #195178 is a reply to message #195176] Wed, 27 September 2006 05:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No it won't.
Not only is it the same as part of @Sandeepks reply, but you've still got the Date mask wrong.
Re: Getting error [message #195274 is a reply to message #195178] Wed, 27 September 2006 13:05 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Apparently I was too succinct.
Re: Getting error [message #195276 is a reply to message #195110] Wed, 27 September 2006 13:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Wed, 27 September 2006 09:09

[Drat - beaten to it by Frank. Curse these Admins!]


Only 70 posts to go (at the time of this writing), so be careful what you say.
Previous Topic: Help with simple(??) query on two columns
Next Topic: insert data into table
Goto Forum:
  


Current Time: Sat Dec 10 20:22:10 CST 2016

Total time taken to generate the page: 0.25724 seconds