Error: "Error converting bulk data (type mismatch or invalid character for the specified code page) for row 2, column 1 (date)."

Question

For SQL Server 2008 R2, is it possible to import a CSV file with a date in the format "MM / DD / YYYY" using "bulk insert"? Or do I need to modify my .csv file to have the format that SQL Server 2008 R2 expects?

I am doing the following in Microsoft SQL Server 2008 R2:

BULK
INSERT dbo.TCsvOptionContracts
FROM 'C:\x.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FORMATFILE = 'C:\format.xml',
FIRSTROW = 2
)
GO

And I get this error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Date).

The date in this column of the .csv file is in the form MM / DD / YYYY, that is, "02/16/2011".

ps I generated a format file with the following command, however I did not see any option to specify the date format MM / DD / YYYY for the violation column:

bcp Engine..TCsvOptionContracts format nul -c -x -f C:\format.xml -t, -T

Answer 1

"" "smalldatetime" "datetime". , 100%.

:

• 1: , ( ).
• 2. LINQ-to-Entities "datetime" , , "smalldatetime", ( , , ).

Answer 2

, SQL 2008 R2, : http://support.microsoft.com/kb/968215 .

Answer 3

FROM 'C: \ x.csv' when loading bulk, it is important to make sure that the file is in the same directory as the SQL server. if the SQL server is located in c: \, then the file must be in C :. if the SQL server is in D: \, then the file must be in D :. I found that it helps alot