Handling datetime with bcp and format file

Question

I'm using bcp to import a flat file into an SQL Server 2005 database.

I'm running into a problem with datetime fields.

My format file field is specified as:

<COLUMN SOURCE="15" NAME="DATEOFSERVICE" xsi:type="SQLDATETIME"/>

My data file has dates formatted as: 19820101

However, some (many) are filled with 00000000

The 00000000 dates fail to import correctly due to a type mismatch.

Is there a way to specify my format file to handle the zeroed out dates? Or a way to tell bcp to enter a default if it comes across 00000000?

Answer 1

I would suggest that you create a temp table where the date column is specified as a varchar data type. Then, BCP your data to the temp table. After the data is loaded in to the temp table, you can scrub the date (replacing 00000000 with NULL, for example). Finally, once your data is validated, copy it to the real tables.

This may not work real well for extremely large data files, but I'm not sure how else you would accomplish this either.

Answer 2

I would use OPENROWSET with a case statement to do the translation while importing the data.