Year and century being swapped when inserting a date into Oracle

Question

4.00/5 (1 vote)

See more:

I have the following table in Oracle 11g:

SQL

create table tb_dates ( dt_date date )

And the following code in C# 4.5:

SQL

DataTable dt = new DataTable();
DataColumn dc = new DataColumn();
dc.DataType = Type.GetType("System.DateTime");
dc.ColumnName = "dt_date";
dt.Columns.Add(dc);
for (int i = 0; i < 5; i++)
{
    DataRow dr = dt.NewRow();
    dr["dt_date"] = System.DateTime.Now;
    dt.Rows.Add(dr);
}

using (OracleBulkCopy bulkCopy = new OracleBulkCopy("data source=orcl;user id=scott;password=tiger", OracleBulkCopyOptions.Default))
{
    bulkCopy.DestinationTableName = "tb_dates";
    bulkCopy.WriteToServer(dt);
}

The dates in the table must be 2014/05/15. But, when I do the select in Oracle, I have 1420/05/15. The year has been reversed.

I'm using Oracle 11g and the Oracle ODP.NET 12 client. What I have to do for this code work correctly?

Posted 20-May-14 8:40am

JimVas2005

Updated 20-May-14 9:16am

Maciej Los

v2

Add a Solution

Comments

Vedat Ozan Oner 20-May-14 15:37pm

can you tell me what you get when you run select to_char(dt_date, 'YY') from tb_dates

JimVas2005 21-May-14 17:15pm

I get: 20

Vedat Ozan Oner 22-May-14 3:34am

oops, that is bad. because that means you are inserting year 1420 actually, not 2014 as expected. that is weird since this conversion is handled by odp.net driver. you can try to insert one by one instead of bulk insert to see what happens when you do so. another option, you can set datetime value as formatted string like dr["dt_date"] = System.DateTime.Now.ToString("yy/MM/dd HH:mm:ss"); assuming that your oracle default datetime format as in the date format string. but that is really weird.

JimVas2005 22-May-14 12:19pm

When I execute this code:
string conexionDestino = "data source=orcl;user id=scott;password=tiger";
using (OracleConnection connection = new OracleConnection(conexionDestino))
using (OracleCommand cmd = new OracleCommand("insert into tb_dates values (:fecha)", connection))
{
connection.Open();
DateTime fecha = DateTime.Now;
cmd.Parameters.Add("fecha", fecha);
int rc = cmd.ExecuteNonQuery();
}
The date in the table is 22/05/2014; the correct date
What is the difference between the bulkcopy and the direct insert?

Vedat Ozan Oner 22-May-14 12:33pm

Simply I don't know. But I think you have just found a bug in odp.net. Congradulations :) you should report it to the odp.net development team. Before reporting it would be better to check if there exists any option about date time format for bulk insert.

Maciej Los 20-May-14 15:42pm

What regional settings?
docs.oracle.com/cd/B19306_01/install.102/b14317/gblsupp.htm

JimVas2005 21-May-14 17:18pm

In RegEdit HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient12Home1
The key NLS_LANG=SPANISH_SPAIN.WE8MSWIN1252

Cătălin-Ionuț Sava 7-Oct-14 4:33am

JIM, i have the same problem. have you resolve it?

JimVas2005 28-May-15 13:29pm

No. I'd tried to contact Oracle Developer Team of .NET but they told me that they was very busy. I had to change the way that I stored the DateTime field, I'd changed to a Numeric field.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11854056 · Answer 1 · 2015-07-21T21:18:00

have problem also.
you can simply change halves of year in code.

C#

foreach (DataColumn dc in dataTable.Columns)
{
    if (dc.DataType == typeof(DateTime))
    {
        foreach (DataRow dr in dataTable.Rows)
        {
            object value = dr[dc];
            if (!Convert.IsDBNull(value))
            {
                DateTime d = (DateTime)dr[dc];
                int year;
                int century = Math.DivRem(d.Year, 100, out year);
                dr[dc] = new DateTime(year * 100 + century, d.Month, d.Day);
            }
        }
    }
}