Search Tools Links Login

Resolution to the "ORA-01843 Not a valid month" Error


Finally a valid resolution to this strange Oracle error.

Original Author: John Galanopoulos

Code

The "ORA-01843" Error; How-Not-To


 


I have seen many developers (specifically those who deal with ORACLE
backends
) knocking themselves out (i was one of them) with this strange
error while trying to execute an SQL query with a date field in it.


For some strange reason, Oracle seems to react with your date format
'mm/dd/yyyy' (or 'dd/mm/yyyy')
and raises this error.


The first thing i did, was to check whether the registry value
HKEY_LOCAL_MACHINESoftwareORACLE ls_date_format  was of the format
"dd/mm/yyyy".


And it was!! After i tried many things, i returned to that key and i
changed the format. Still nothing. This key seems to have no effect to the way
ORACLE databases handle date values.


I went back to my source and after the line where my connection to the
ORACLE database was initialized, i wrote :


strSQL  = "ALTER SESSION SET NLS_DATE_FORMAT =
'DD/MM/YYYY'"


{The following line applies to Oracle Objects for OLE ( aka OO4O). If
you use ADO, RDO, DAO, ODBC API etc etc write it in it's relative format}.


 myOraDB.ExecuteSQL (strSQL)


  
  
  Guess what ppl. It worked!


All my Insert/Update/Delete SQL statements that included a date value where
properly inserted into the Oracle database table. No errors so no problems :-)


About this post

Posted: 2003-06-01
By: ArchiveBot
Viewed: 107 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.