Sql query on orcale DB error (literal does not match format string)

Question

Post reply

Sql query on orcale DB error (literal does not match format string)

quillis131

Hall of Fame

Points: 3706
More actions
April 6, 2011 at 11:16 am

#235288

select a,b,c,d,e, ADDED_DATE, CLIENT_TYPE
from table1(orcaletable)
where CLIENT_TYPE <> 4 and ADDED_DATE> ALL('4/4/2011 8:20:51 PM','DD/MM/YYYY HH:MI:SS PM')
error messaage :
ORA-01861: literal does not match format string
ORA-01861: literal does not match format string (OraOLEDB)
can anyone help me in this

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

souldn't the data be ('04/04/2011...

or the pattern be M/D/YYYY...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

quillis131 Hall of Fame Points: 3706 More actions · Answer 2

Table1 (column ADDEDDATE) is in the form

10/1/2005 12:00:00 AM

.

8/18/2007 12:00:00 AM

.

i guess, we can't do when the column is in the above form

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

quillis131 (4/6/2011)
Table1 (column ADDEDDATE) is in the form
10/1/2005 12:00:00 AM
8/18/2007 12:00:00 AM

What's the datatype of ADDED_DATE column? is it a DATE or a VARCHAR2 column?

If ADDED_DATE is a DATE column please try..

TO_DATE('04/04/2011 8:20:51 PM','DD/MM/YYYY HH:MI:SS AM')

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.