sql-server-2008importssiswizard

SQL Server Import Export wizard - error for datetime - specific values

I am using SQL Server 2008 Import Export wizard to bulk import a text file.

The text file contains more than 9 Lakh records with column delimiter | and row delimiter / terminator as {LF}

Everything is working fine, except in one case: there is one column in the table with datatype datetime and there are few records in text file having dates like 01/07/1861, 09/08/1865 etc. and the wizard fails to import these type of records giving error "Invalid Date Format"

Can any one assist me?

Thanks and Regards, Pratik

UPDATE -

The problem is with only date value 08/08/1696.

Even if I try to run simple query like following:

select convert(datetime,'08/08/1696', 101) it gives error like “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.”

Solution

  • The best thing to do is to import everything to a staging table with all column data types as NVARCHAR or VARCHAR.

    Once this is done you can then convert the data easily from string to date.

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/47fc07d2-37fe-4dd8-b57f-3867cd57e2b0