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

3
votes

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

2
sql-server-2008importssiswizard
Lakh = 100,000 for people like me who were ignorant - billinkc
What data type is that column defined as in the Flat File Connection Manager? DT_DBDATE, DT_DBTIMESTAMP, DT_DBTIMESTAMP2, etc. My guess at this moment is that isn't a valid range for the SSIS datetime type you selected but I'll need to look up the type mappings once I get to work. - billinkc
it is mapped to DT_DBTIMESTAMP - pratik
And what is the destination column defined as? datetime, datetime2, date? - billinkc
Exactly there is the problem, as billinkc suggested. '08/08/1696' does not fall within the expected range of the datetime SQL type. The date must be between '01/01/1753' and '31/12/9999' (msdn.microsoft.com/en-us/library/ms187819.aspx) Use datetime2 instead which is valid from '01/01/0001' - luviktor

2 Answers

9
votes

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

0
votes

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