I got this error: ORA-01843 Oracle date format picture ends before converting entire input string

The variable fileDateTime was a defined as a normal C# DateTime.

Code Failed with ORA=01843

                    commSQL.CommandText = "UPDATE MY_TAB " + 
                        " SET DATE_FILE_PROCESSED = TO_DATE('" + fileDateTime + "', 'MM/DD/YYYY HH:MI:SS')" +  
                        " WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '" + PONum + "'";  

Above generated this SQL Update – Notice the “AM” on the end of the date time:

UPDATE MY_TAB  SET DATE_FILE_PROCESSED = TO_DATE('9/17/2015 8:01:51 AM', 'MM/DD/YYYY HH:MI:SS') 
WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '118686'

Corrected Code Works

Note that I changed Oracle date from HH to HH24.

                    commSQL.CommandText = "UPDATE MY_TAB " + 
                        " SET DATE_FILE_PROCESSED = TO_DATE('" + fileDateTime.ToString("MM/dd/yyyy HH:mm:ss") + "', 'MM/DD/YYYY HH24:MI:SS')" +  
                        " WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '" + PONum + "'";  

UPDATE MY_TAB  SET DATE_FILE_PROCESSED = TO_DATE('09/17/2015 08:01:53', 'MM/DD/YYYY HH24:MI:SS') 
WHERE DATE_FILE_PROCESSED IS NULL AND CUST_PO_NO = '118690'

References: Microsoft Dater Formats   Oracle TO_DATE function

 

 

Filed under: C#