none
BULK INSERT format file error when importing SQLDATE type

    Question

  • I'm trying to do a BULK INSERT in SQLServer 2008 running on Windows 7 x64.

    I have a column of type DATE and when bcp creates the format file for the table it makes the xsi:type=SQLDATE.  Then when importing the file, the date (as '20051231') is not translated correctly.  For some reason bcp returns the same value for every date in the file resulting in a 'duplicate PK'.

    When I redefine the column to be type SMALLDATETIME bcp makes the type SQLDATETIM4, which BULK INSERTS correctly into the table.

    Have checked posts on this forum to no avail.  Any ideas?  Thanks

    UPDATE WITH ADDITIONAL INFORMATION:

    After more research I've discovered more on this issue.  When using type DATE for the column and type SQLDATE in the format file all input date strings in the form 'yyyymmdd' (for example '20051231') are translated by BULK INSERT as 1900-01-01.  It's as though the input dates are not valid and are being set to the lowest possible date by default.  However, if I re-format the input dates to the form 'yyyy-mm-dd' they are properly cast to DATE and are inserted into the table correctly.

    As mentioned above, using SMALLDATETIME works as expected, the problem is only with the new 2008 DATE type.

    So... the problem appears to be a bug in either bcp or BULK INSERT.

    Anyone know how I can report this to the SQL Server development team?

    Thanks
    Tuesday, January 12, 2010 10:22 PM

All replies