Cast .NET DateTime to OracleDate

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) + "'"

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();
    }
}

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')";