It takes me some time to figure out how a string / numeric value can be converted to a datetime value inside the Derived Column Transformation Editor of SSIS, and so, I would like to share my $0.20 here (in order to save others’ $0.20). :)
Here, the original TradeDate (e.g., 20090316) and TradeTime (e.g., 230533) are numeric values.
This one returns a datetime value with TradeDate only.
(DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)TradeDate,1,4) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,5,2) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,7,2))
And here’s how I obtain the datetime value including both TradeDate and TradeTime. I intentionally use the function DATEADD() to add hour, minute, and second to TradeTime.
DATEADD("ss", TradeTime - ROUND(TradeTime / 100, 0) * 100, DATEADD("mi", ROUND(TradeTime / 100, 0) - ROUND(TradeTime / 10000, 0) * 100, DATEADD("Hh", ROUND(TradeTime / 10000, 0), (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,8)TradeDate,1,4) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,5,2) + "-" + SUBSTRING((DT_WSTR,8)TradeDate,7,2)))))
You may also concatenate both TradeDate and TradeTime into a string that can be read by DT_DBTIMESTAMP.
===== For more information =====
~ SQL Server Product Samples: SSIS
http://www.codeplex.com/MSFTISProdSamples/Wiki/View.aspx?title=SS2008!Execute%20SQL%20Statements%20in%20a%20Loop%20Sample%20Package&referringTitle=Home
~ Converting Strings to Dates in the Derived Column Component
http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html