SSIS – Converting string / numeric (yyyymmdd and hhmmss) to datetime inside Derived Column Transformation Editor

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