Date format issue while inserting c# datetime to oracle table

Question

I have a csv file and I need add one more column(as-of-date) and save them to an oracle table using c#. What I did was that I add data to Datatable and used OracleBulkCopy to insert all data to db table.

Now the issue is when I check my oracle table in Toad, the first column shows me 10/08/1320 and obviously oracle cannot store date which is older than 1900. And when I ckick the field from Toad, it tells me that "The specified date is out of range for supported dates of ....".

Below are three other solutions I tried and all had the same issue.

using Oracle.DataAcccess.Client;

//Solution 1

DataTable dt = new DataTable();

dt.Columns.Add("Product_Date",typeof(DateTime));//The first column of my oracle table is Date
dt.Columns.Add(....);
 ...

//loop my csv file
DataRow dr = dt.NewRow();
dr[0] = new DateTime(2013,10,8);
dr[1] = ...;
...
dt.Rows.Add(dr)

OracleBulkCopy bc = new OracleBulkCopy(myConn);
bc.DestinationTableName = "TableName";
bc.WriteToServer(dt);

//Solution 2

DataTable dt = new DataTable();

dt.Columns.Add("Product_Date",typeof(OracleDate));//The first column of my oracle table is Date
dt.Columns.Add(....);
...

//loop my csv file
DataRow dr = dt.NewRow();
DateTime dtime = DateTime(2013,10,8);
dr[0] = new OracleDate(dtime);
dr[1] = ...;
...
dt.Rows.Add(dr)

OracleBulkCopy bc = new OracleBulkCopy(myConn);
bc.DestinationTableName = "TableName";
bc.WriteToServer(dt);

//Solution 3

DataTable dt = new DataTable();

dt.Columns.Add("Product_Date",typeof(string));//The first column of my oracle table is Date
dt.Columns.Add(....);
...

//loop my csv file
DataRow dr = dt.NewRow();
DateTime dtime = DateTime(2013,10,8);
dr[0] = dtime.ToString("ddMMMyy");
dr[1] = ...;
...
dt.Rows.Add(dr)

OracleBulkCopy bc = new OracleBulkCopy(myConn);
bc.DestinationTableName = "TableName";
bc.WriteToServer(dt);

Grayson Shelton · Accepted Answer · 2015-02-05 16:26:40Z

2

I solved that problem by using to_timestamp instead of to_date...

DateTime date = Convert.ToDateTime(txtTrainDate.Text);
DateTime time = Convert.ToDateTime(ddTrainTime.SelectedValue);
DateTime dtCOMPLTDTTM = new DateTime(date.Year, date.Month, date.Day, time.Hour, time.Minute, time.Second);
string strCOMPLTDTTM = dtCOMPLTDTTM.ToString("dd.MM.yyyy HH:mm:ss")

Then, in the Oracle Insert...

to_timestamp('" + strCOMPLTDTTM + "', 'DD.MM.YYYY hh24:mi:ss'),"

answered Feb 5, 2015 at 16:26

Grayson Shelton

811 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date format issue while inserting c# datetime to oracle table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related