I thought I had a handle on bcp format files and bulk inserts until I was required to insert an 8 character field (20020622) from a text file to a DATETIME field in a sql database table. I tried using the single quotes, which work on regular inserts: '20020622' but I am getting this error:

----------------------------

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 12 (Hire_Dt).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 12 (Hire_Dt).

----------------------------

for every line of the text file. I am using a format file to sort out the order of the columns and trim off extra characters in the text file, and I am not sure how the date fields should look in the format file. currently those fields in the format file look like this:

----------------------------

12 SQLCHAR 0 10 "," 8 Hire_Dt SQL_Latin1_General_CP1_CI_AS

----------------------------


I made the Host File Data Length 10 to account for the quotes. Do you think that is a mistake? Also, the SQL code, which i am running in Microsoft SQL Query Analyzer looks like this:

-----------------------------

BULK INSERT PAY_WORK
FROM '\\hostname\d$\temp2.txt'
WITH (
FORMATFILE = '\\hostname\d$\payrollformat.fmt'
)

-----------------------------

Am I using the wrong Host file data type or the wrong Collation in my format file??? If anyone knows what the format file should look like, please let me know.

Thanks,

Frances