CODEWITHSUNDEEP
sql-server-2008importssiswizard
pratik
pratik

Reputation: 4479

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.”

Upvotes: 3

Views: 34085

Answers (2)

Julio Pereira
Julio Pereira

Reputation: 90

I would only suggest to check the actual format of your data in the source database. In my case I solved the issue by passing nullable datetime2 to date fiels. This worked because the wizard was not reading correctly the source database and it was passing this wrong format (the source database was using datetime2, and the wizard was passing smalldatetime, creating the error).

Upvotes: 0

Kip Real
Kip Real

Reputation: 3459

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

Upvotes: 9

Related Questions