BULK Insert problem with DateTime field

I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:

Taxonomic_Units Table:

INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind

I use the following SQL Statement to BULK INSERT:

BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
KEEPIDENTITY,
KEEPNULLS

)

Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:

50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||

The error is:

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

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

llzamboni wrote:

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

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

For me, that works just fine...

SELECT CAST('1996-06-13 14:51:08.0' AS DATETIME)

Are you *sure* that row is the problem?

|||

It seems like BULK INSERT is having difficulties with the (sort of) malformed date 1996-06-13 14:51:08.0

If you remove the last .0 or add two zeroes so it becomes .000 then BULK INSERT will insert the row.

However, if you instead use BCP, no changes are needed.
Apparently, BCP isn't as cranky as BULK INSERT in this case.

/Kenneth