Cast .NET DateTime to OracleDate

Question

I am trying to insert a .NET DateTime value into an Oracle DATE column using ODP.NET. Thus far, I have tried using the OracleDate() constructor to cast the .NET DateTime like so:

new OracleDate(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTimeNow.Hour, DateTime.Now.Minute, DateTime.Now.Second)

When I try this, it inserts the correct year, month, and day into the column but the time is always set to midnight. How do I insert the correct time along with the date?

Without parameters, the SQL looks like this (DateTime.Now is used for clarity, otherwise I'd just use SYSDATE):

"update mytable set timestamp = '" + new OracleTimeStamp(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTimeNow.Second) + "'"

Solution 1

According to this you should use OracleTimeStamp as OracleDbType to achieve what you want...

OracleDate is without time portion... this behaviour started sometime ago (IIRC Oracle 8 or so) with the JDBC drivers... with the JDBC drivers there was/is some weird workaround which I don't remember it right now... I don't know if there is a workaround for ODP.NET... OracleTimeStamp is the officially supported way for dates with time portions...

EDIT - after OP added SQL statement:

First of all this statement contains two problems - never name a column/table like a reserved word (i.e. timestamp)... the other problem is the lack of using a parameter which in this case won't lead to SQL injection but still is bad practice and leads (if this same statement is used multiple times) to a minimal loss of performance...

IF you still want to use it that way THEN this will work:

"update mytable set timestamp = TO_DATE ('" + DateTime.Now.ToString ("yyyyMMddHHmmss") + "', 'YYYYMMDDHH24MISS')";

Solution 2

You should be using parameters for this. There's also no need to use the Oracle-specific datatypes when an equivalent .NET datatype will do. This has the advatage of making it easy to swap out ODP.NET for a different driver at a later date, as most other drivers work with the native .NET datatypes.

using (OracleConnection conn = new OracleConnection("your connection string here")
{
    string query = "update mytable set timestamp = :foo";

    using (OracleCommand cmd = new OracleCommand(query, conn))
    {
        cmd.Parameters.Add(":foo", DateTime.Now);

        cmd.ExecuteNonQuery();
    }
}

Comments

[kittyhawk]

Using the OracleTimeStamp type gives me the Oracle error: "date format picture ends before converting entire input string."

[Yahia]

please show the exact SQL statement - perhaps it needs a minimal modification

[tsells]

I agree. There is no reason to use the timestamp.

[SoftwareSavant]

I am having a similar problem. Should I just be entering a datetime? Right now I enter a string formqted with a time on the end.

[Cory Grimster]

@DmainEvent: Yes, all of the ADO.NET drivers & databases that I've used would work with the code above, assuming that the "mytable.timestamp" column above has the appropriate datatype (for example DATE in Oracle or TIMESTAMP in Postgres).