Data Convertion for Date Column

[JerryKlmns]

Hi everyone.

I'm trying to create an SSIS that reads a flat file into an SQL2005 table. The problem exists at a column of the flat file where a date is formatted like yyymmdd and should be going in a datetime field of the table.

Is there an appropriate approach for that?

TIA

 

[JerryKlmns]

is formatted like yyymmdd

is formatted like yyyymmdd

 

[SQLBill]

What we normally do with Date values coming from non-SQL Server sources is to use a staging table first.

Set up a staging table with the datetime column as varchar(20).
Pull the data from the source to the staging table.
Copy the data from the staging table to the production table.

We need to do this a lot for Oracle sources as their datetime values cause issues converting into SQL Server DATETIME. But once the datetime values are in a SQL Server VARCHAR column, SQL Server can usually convert it to DATETIME internally when you move it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[JerryKlmns]

I was hopping to avoid that, but since it is the way to do it, I'll enjoy it.

Thanx SQLBill