SQL Server 2008 BULK INSERT DateTime Error

Question

I am trying to use BULK INSERT in SQL Server 2008 to import a TSV (Tab Separated Value) file.

Here is my script:

USE ABC
GO

CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
TodaysDate DATETIME)
GO

BULK
INSERT CSVTest
FROM 'd:\csvtest.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

--Check the content of the table.
SELECT *
FROM CSVTest
GO

--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

DROP TABLE CSVTest
GO

Here is the content of the file d:\csvtest.txt:

1   James   Smith   16/10/2010 04:45:35
2   Meggie  Smith   16/10/2010 04:45:35
3   Robert  Smith   16/10/2010 04:45:35
4   Alex    Smith   16/10/2010 04:45:35

Unfortunately, I get the following error:

Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (TodaysDate).

Obviously, I need to convert the date format in my TSV file to a format that is acceptable to DATETIME.

Can someone please help me?

Answer 1

I am wondering if this has to do with it expecting months first, and not days in your date format of 16/10/2010 (i.e., it expects 10/16/2010). You can try doing this before the BULK INSERT:

SET DATEFORMAT dmy;