ORA-01843 not a valid month with timestamp

[Vercuski]

I am working with the OracleParameter class writing a dynamic SQL insert statement. I have a third party form where a user can enter a date/time and an ID and submit the value. The data values that are entered into the form fields appear as follows:

DATE VALUE = 12/31/2011 12:00:00 AM
ID VALUE = 16

The code for creating the OracleParameter is using the "OracleDbType.TimeStamp" as the parameter type (the Date column on the table is defined as TIMESTAMP(6) )

When I receive the value in my code and assign it to the parameter, the date value above appears as follows:

2011-12-01T00:00:00

even though it was entered into the form field as

12/31/2011 12:00:00 AM

I am assuming that the 3rd party software is changing the date to this format.

When I execute the INSERT statement I receive the following error:

Message: Devart.Data.Oracle.OracleException (0x80004005): ORA-01843 not a valid month at Devart.Data.Oracle.OracleTimeStamp


I'm rather new to Oracle so I'm not sure how timestamps should be formatted. How do I go about creating a correct timestamp from the "2011-12-01T00:00:00" value that I am assigning to the OracleParameter.Value property in my C# code.

Thank you for your help!

[Pinturiccio]

You can specify the TimeStamp format:

Code: Select all

comm.Parameters.Add("ts", OracleDbType.TimeStamp, 6, "ts");
comm.Parameters["ts"].Value = OracleTimeStamp.Parse("2011/12/01T00:00:00".Replace('T', ' '), "YYYY.MM.DD HH24.MI.SS", OracleDbType.TimeStamp);