Date Conversion - Flat File - YYYYMMDD

Hi,

What is the new way to transform flat file dates into SQL datetime datatype.Being average user in SQL 2000 DTS I would simply use "Date Time String Transformation Properties" and transform the date into the format I need, in SSIS I haven't found an elegant way of doing this.

My thoughts are to use “data conversion” utilizing substring expressions…

Thanks the help

Bill

Use a derived column transformation to substring the date field and then concatenate the parts together. Once that's done cast it to a datetime field.

Something like:

(DT_DBTIMESTAMP)(substring([yourDateField],5,2) + "/" + substring([yourDateField],7,2) + "/" + substring([yourDateField],1,4))

|||

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

bmilstead,

In my case, I declared the metadata for the date columns in the flat file as DB_TIMESTAMP, and then used a derived column to filter invalid dates using an expression for the [Begin Date] Column

ISNULL([Begin Date]) || (DT_I4)DATEPART("yyyy",[Begin Date]) < 1753 || (DT_I4)DATEPART("yyyy",[Begin Date]) > 9999 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Begin Date]

Thanks

Subhash Subramanyam

Right, but the format of the dates in the flat file are not DB_TIMESTAMP compatible. (YYYYMMDD)