converting a number into a date type using an expression

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!

Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.

Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.

I'm just getting started with SQL 2005 so I don't know how to do this using an expression.

Any help would be greatly appreciated.

Thanks,

Gray

I think your approach sounds valid. You're going to have to parse it with substring and build it into a date format. Then you can cast the string to a date/time field.

(DT_DBTIMESTAMP)(substring([YourColumn + 19000000],x,y) + "/" + .......)

|||

Hi Phil,

Thanks for the info ... it really helped ... here's my final expression ...

(DT_DBTIMESTAMP)(SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),5,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),7,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),1,4))

Thanks again,

Gray