Home » SQL & PL/SQL » SQL & PL/SQL » to_date
to_date [message #253216] Mon, 23 July 2007 02:59 Go to next message
nhaziq
Messages: 2
Registered: July 2007
Location: Brunei
Junior Member
I got error message ORA-01861: literal does not match format when I search the staff name using the date

for the date column name, Im using varchar2.

how can I away from that message without changing the datatype into date? I cannot change it into date datatype because the data was there already.

thank you
Re: to_date [message #253217 is a reply to message #253216] Mon, 23 July 2007 03:01 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So correct the format string to match the literal.

For a better explanation, post SQL*Plus session so that we could see what you did.
Re: to_date [message #253222 is a reply to message #253217] Mon, 23 July 2007 03:09 Go to previous messageGo to next message
nhaziq
Messages: 2
Registered: July 2007
Location: Brunei
Junior Member
vSambungan = "where "

Set rs = New ADODB.Recordset
strSQL = "select Id,Name,Department," _
& "Leave,Total_Leave,Leave_From,Leave_Til " _
& "from SMK_Leave " _

If txt(txtPegID) <> "" Then
strSQL = strSQL & vSambungan & "lower(ID) like lower('" & txt(txtPegID) & "') "
vSambungan = "and "
End If

If txt(txtFrom) <> "" Then
strSQL = strSQL & vSambungan & "to_date(Leave_From,'dd/mm/yyyy') >= to_date('" & txt(txtFrom) & "','dd/mm/yyyy')"
vSambungan = "and "
End If

If txt(txtTil) <> "" Then
strSQL = strSQL & vSambungan & "to_date(Leave_Til,'dd/mm/yyyy') <= to_date('" & txt(txtTil) & "','dd/mm/yyyy')"
End If

strSQL = strSQL & "order by ID desc"
rs.Open strSQL, cn

I cannot change the datatype from varchar2 into date because the data was already in the database.

Is there any solution?
Re: to_date [message #253224 is a reply to message #253222] Mon, 23 July 2007 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

2/ Use bind variables of appropriate type and do not concatenate.
This will guarantee you:
a) optimal performances
b) No conversion problem.

3/ If Leave_From column is of date datatype, using TO_DATE on this column is meaningless.

4/ I don't see any reason you can't use a static statement with bind variables, prepare it, execute it, fetch it.

Regards
Michel


Regards
Michel
Re: to_date [message #253274 is a reply to message #253222] Mon, 23 July 2007 06:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And, in addition to Michel's remarks:

- ask SQLServer questions in a SQLServer forum.
Previous Topic: Declare Variable
Next Topic: calling a DDL sql script from plsql block
Goto Forum:
  


Current Time: Wed Dec 07 08:29:20 CST 2016

Total time taken to generate the page: 0.14287 seconds