I have a String to Date conversion problem using SQL Bulkcopy in asp.net 3.5 with C#
I read a large CSV file (with CSV reader). One of the strings read should be loaded into a SQL server 2008 Date column.
If the textfile contains for example the string '2010-12-31', SQL Bulkcopy loads it without any problems into the Date column.
However, if the string is '20101231', I get an error:
The given value of type String from the data source cannot be converted to type date of the specified target column
The file contains 80 million records so I cannot create a datatable....
SqlBulkcopy Columnmappings etc. are all ok. Also changing to DateTime does not help.
I tried
SET DATEFORMAT ymd;
But that does not help.
Any ideas how to tell SQL Server to accept this format? Otherwise I will create a custom fix in CSV reader but I would prefer something in SQL.
update
Following up on the two answers, I am using SQL bulkcopy like this (as proposed on Stackoverflow in another question):
The CSV reader (see the link above on codeproject) returns string values (not strong typed). The CSVreader implements System.Data.IDataReader so I can do something like this:
using (CsvReader reader = new CsvReader(path))
using (SqlBulkCopy bcp = new SqlBulkCopy(CONNECTION_STRING))
{ bcp.DestinationTableName = "SomeTable";
// columnmappings
bcp.WriteToServer(reader); }
All the fields coming from the iDataReader are strings, so I cannot use the c# approach unless I change quite a bit in the CSVreader
My question is therefore not related on how to fix it in C#, I can do that but i want to prevent that.
It is strange, because if you do a in sql something like
update set [somedatefield] = '20101231'
it also works, just not with bulkcopy.
Any idea why?
Thanks for any advice,
Pleun
See Question&Answers more detail:
os