convert a date stored as a string into a datetime

Hello forum,

Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the “data conversion” fail. The string type form of the date is ‘yyyy-mm-dd’ and the desired result for use in a Union All is ‘dd/mm/yyyy 12:00:00AM.’This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.

All advice/help welcomed.

Ian

Use the Derived Column transform, and add this expression:

Code Snippet

(DT_DATE)((SUBSTRING(StringDate,6,2) + "-" + SUBSTRING(StringDate,9,2) + "-" + SUBSTRING(StringDate,1,4)))

Tip: Because there is no domain integrity inherent in the string date format, be certain to include an error output on your Derived Column transform.

|||

Use a dervide column with substring to re-order the date format; at the end cast it as date:

Code Snippet

(DT_DATE)(SUBSTRING(StrDAte,9,2) + "/" + SUBSTRING(StrDAte,6,2) + "/" + SUBSTRING(StrDAte,1,4))

|||Since this is a common topic today, I blogged on it, with a little more detail than what is posted here: http://bi-polar23.blogspot.com/2007/05/having-trouble-getting-date.htmlsqlsql