Proper method to update Oracle TIMESTAMP(0) WITH TIME ZONE value in database

Question

I am attempting an update to a table with a column of type TIMESTAMP(0) WITH TIMEZONE.

I have tried several methods without success as the way the TIMESTAMP is written to the database does not have the offset format such as -05:00 for USA Eastern Time. It's saved using AMERICA/NEW_YORK as the time zone, which causes issues with another application that cannot handle this properly.

CURRENT: 28-NOV-16 10.51.43.000000000 AM AMERICA/NEW_YORK

DESIRED: 28-NOV-16 10.51.43.000000000 AM -05:00

The many postings here speak mainly to formatting the data when it's retrieved from the database; other examples are described with SqlPlus and not in C#.

Using(OracleConnection conn = new OracleConnection(......))
{
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.BindByName = true;
        cmd.CommandText = "update customer set email_addr = :EMAIL, modified_date= SYSDATE 
    where cust_id = :CUSTID";

        conn.Open();
        cmd.Parameters.Add("EMAIL", OracleDbType.Varchar2).Value = txtEmail.Text;
        cmd.Parameters.Add("MODIFIED_DATE", OracleDbType.Varchar2).Value = OracleDate.GetSysDate().ToOracleTimeStamp();
        cmd.Parameters.Add("CUSTID", OracleDbType.Decimal).Value =Convert.ToDecimal(Session["ID"]);

    cmd.ExecuteNonQuery();
}

I have also tried modified_date=to_timestamp(:modified_date, 'MM/DD/YYYY HH:mi:ss') but this generates exception as not a properly formatted time zone.

What is the proper method to accomplish this in C#? Should a date/timestamp column always be written with a conversion from a string?

Answer 1

Looks like you did a typo in your code. You specified 2 bind variables but you try to bind 3 values.

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.BindByName = true;
cmd.CommandText = "update customer set email_addr = :EMAIL, modified_date= :MODIFIED_DATE
where cust_id = :CUSTID";

conn.Open();
cmd.Parameters.Add("EMAIL", OracleDbType.Varchar2).Value = txtEmail.Text;
cmd.Parameters.Add("MODIFIED_DATE", OracleDbType.TimeStampTZ).Value = OracleDate.GetSysDate().ToOracleTimeStamp();
cmd.Parameters.Add("CUSTID", OracleDbType.Decimal).Value =Convert.ToDecimal(Session["ID"]);
cmd.ExecuteNonQuery();

OracleDbType is not Varchar2, it must be TimeStampTZ.

However, it looks like you are using Oracle Data Provider from DevArt. When I check the documentation it looks like they do not support data type TIMESTAMP WITH TIME ZONE.

I see several workarounds.

Set your SESSIONTIMEZONE to -05:00 before you run your operations on database, for example as this:

cmd.CommandText = "ALTER SESSION SET TIME_ZONE = '-05:00'";
cmd.ExecuteNonQuery();

Then your C# code can be like this:

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.BindByName = true;
cmd.CommandText = "update customer set email_addr = :EMAIL, modified_date= CURRENT_TIMESTAMP
where cust_id = :CUSTID";

conn.Open();
cmd.Parameters.Add("EMAIL", OracleDbType.Varchar2).Value = txtEmail.Text;
cmd.Parameters.Add("CUSTID", OracleDbType.Decimal).Value =Convert.ToDecimal(Session["ID"]);
cmd.ExecuteNonQuery();

CURRENT_TIMESTAMP returns current time in you session time zone as TIMESTAMP WITH TIME ZONE data type.

You can also specify time zone in SQL, example:

cmd.CommandText = "update customer set email_addr = :EMAIL, 
       modified_date= SYSTIMESTAMP AT TIME ZONE '-05:00'
   where cust_id = :CUSTID";

When you update a TIMESTAMP WITH TIME ZONE value with TIMESTAMP Oracle will make an implicit cast to TIMESTAMP WITH TIME ZONE using time zone -05:00.

Note, America/New_York has Daylight Saving Time, i.e. you must make these commands more dynamic and switch between -05:00 and -04:00 twice a year (that would be the great befit if you would use time zone regions like America/New_York).

Another workaround could be to run ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-YY HH.MI:SS.FF AM TZH:TZM' at your application which cannot handle time zone names like AMERICA/NEW_YORK.