ORA-01843 not a valid month with timestamp

ORA-01843 not a valid month with timestamp

Postby Vercuski » Thu 05 Jan 2012 14:13

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

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:


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!
Posts: 1
Joined: Thu 05 Jan 2012 13:55

Postby Pinturiccio » Mon 09 Jan 2012 15:16

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);
Devart Team
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Return to dotConnect for Oracle