Home » SQL & PL/SQL » SQL & PL/SQL » Problem with date format (literal does not match format string)
Problem with date format (literal does not match format string) [message #247800] Wed, 27 June 2007 04:47 Go to next message
thecornflake
Messages: 3
Registered: June 2007
Location: UK
Junior Member
Hi,

I'm a VB.NET programmer but a bit of a newbie with Oracle. I've got a program that connects to an Oracle database and runs a simple select using a date. I get the 'literal does not match format string' error when it runs.

The NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMATs are -

DD-MM-YYYY HH24:MI:SS

I've tried formatting the date as this and also with dots instead of slashes as this is the result of selecting one of the dates from the table -

2007.02.20 10:53:58

The code for the connection (we use usernames with the same name as the database) and select statement are -

conn1.ConnectionString = "Provider=MSDAORA.1;" & _
            "Data Source=" & servername & ";" & _
            "User ID=" & uid & ";" & _
            "Password=" & pword & _
            ";Database=" & uid

oracmd.CommandText = "Select count(*) as okcount from pwwlprintqueue where "
oracmd.CommandText &= "wlprocessedstatus in ('P','Q') and wlinsertedtime > '" & maxDate.ToString("yyyy.MM.dd HH:MM:ss") & "'"


As I said I've tried formatting the date in both this format and the same format as the NLS_DATE_FORMAT with the same result. The Oracle version is 9i but it also needs to work on 10g.
Re: Problem with date format (literal does not match format string) [message #247805 is a reply to message #247800] Wed, 27 June 2007 05:03 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
try usining minutes instead of months in the time element (mi not MM)
Re: Problem with date format (literal does not match format string) [message #247814 is a reply to message #247805] Wed, 27 June 2007 05:14 Go to previous messageGo to next message
thecornflake
Messages: 3
Registered: June 2007
Location: UK
Junior Member
Tried that (I think it's mm rather than mi) but still get the same error.
Re: Problem with date format (literal does not match format string) [message #247822 is a reply to message #247805] Wed, 27 June 2007 05:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Try :

oracmd.CommandText = "Select count(*) as okcount from pwwlprintqueue where "
oracmd.CommandText &= "wlprocessedstatus in ('P','Q') and wlinsertedtime > TO_DATE('" & maxDate.ToString("yyyy.MM.dd HH:MM:ss") & "','YYYY.MM.DD HH24:MI:SS')"


2. You are not using bind variables, so your statement is going to be hard parsed for each execution and it may be subject for sql injection as well.

HTH.
Michael
Re: Problem with date format (literal does not match format string) [message #247827 is a reply to message #247822] Wed, 27 June 2007 05:45 Go to previous messageGo to next message
thecornflake
Messages: 3
Registered: June 2007
Location: UK
Junior Member
Thanks very much - that worked fine.

Does TO_DATE just reformat the date again?
Re: Problem with date format (literal does not match format string) [message #247833 is a reply to message #247827] Wed, 27 June 2007 05:56 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. The
maxDate.ToString("yyyy.MM.dd HH:MM:ss") 

is Microsoft formatting function converting MS date to string.

TO_DATE is Oracle function converting string to date.

You still have to deal with bind variables.

Michael
Previous Topic: get last inserted record
Next Topic: ref_cursor error
Goto Forum:
  


Current Time: Mon Dec 05 04:38:45 CST 2016

Total time taken to generate the page: 0.13417 seconds