0

I'm trying to copy the data from source dt to destination datatable. source datatble types are sting and destination datatble types contains datetime along with strings.

datatable dt2=new datatable();
foreach (DataRow row in dt1.Rows)
{            
     dt2.ImportRow(row); //String was not recognized as a valid DateTime.
}

I get String was not recognized as a valid DateTime as destination column type is datetime and is not able to import that row.

Ashu
  • 11
  • 5
  • Personally, I would run (not walk) to "select into": https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-2017 – paulsm4 Feb 07 '19 at 06:43
  • You won't be able to import the data from invalid strings that can't be converted to date/datetime. You will need to parse or fix before casting. – EzLo Feb 07 '19 at 07:13
  • OK.So how do I parse the string value finding the destination schema is datetime? – Ashu Feb 07 '19 at 07:15
  • It will be easier to import the data to the same table structure and then convert using SQL. On C# you will need to detect which columns are date on the destination and try to parse the string and leave as null if it fails. – EzLo Feb 07 '19 at 07:34
  • Now I'm trying to convert to destination datatype using Convert.ToDateTime(row[k.ColumnName]) where value is {4/06/1986 12:00:00 AM .I get the error: String was not recognized as a valid DateTime. – Ashu Feb 07 '19 at 08:40

1 Answers1

0

Use DataTable.Clone() to setup a new DataTable object with the existing schema. Then add any additional columns you might need.

DataTable dt1 = MyData();
DataTable dt2 = dt1.Clone();
foreach(DataRow row in dt1.Rows)
{
    dt2.ImportRow(row);
}
mitchsnitchel
  • 79
  • 7
  • bulkCopy.WriteToServer(dt2); I get the error on BulkCopy: The given value of type String from the data source cannot be converted to type datetime of the specified target column bulkcopy – Ashu Feb 07 '19 at 06:28