how do I import a date like 2009-12-05 11:40:00 into SQL via CSV?

Question

I'm using the Import/Export Wizard to import some data to a table. Total of 2 rows, so I've just been working around this, but I would like to know the answer.

The issue with the Import/Export is the dates. No matter what I do, they fail. The date looks pretty straightforward to me: 2009-12-05 11:40:00. I also tried: 2010-03-01 12:00 PM. Tried DT_DATE and DT_DBTIMESTAMP as a source data type. The target column type is datetime.

The message that I get is:

The data conversion for column "Start_Date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

How do I fix this? Why's the Import/Export Wizard so bad at parsing dates (or is that in my imagination)?

The truly obnoxious thing here is that when you select a date column from a table and save it as a CSV you get a date like '2009-12-05 11:40 AM'. So the import wizard isn't even capable of parsing dates that come from SQL Server. Really? Really?

Answer 1

Added details (realized my description wasn't correct after revisiting the package I had issues with):

The import thing IS pretty bad.

In my case I had incoming data with form matching SQL Server type 126 / ISO8601. That is, in T-SQL, this form:

select convert ( varchar(100), getdate(), 126 )

--> 2009-12-22T16:29:22.123

I was able to import with SSIS using two steps:

1. Replace the "T" with a space " ", using SSIS Derived Column with expression:

   REPLACE(DateColumn,"T"," ")

2. Cast the result to database timestamp [DT_DBTIMESTAMP] using the data conversion transform

Apologies if I caused any confusion.