Converting String values to a DATETIME datatype in SSIS expressions

While transforming a flat file to a strongtyped SSIS dataset I needed a DATETIME value to use in my query later on. Too bad i only have a date and a time string value in my derived column component. I was expecting a solid date time format function to create a DATETIME datatype (like VB’s formatdatetime of C# DateTime.Parse ), too bad none of that is available. You have to do it with straightforward substring, this is the monster is eventualy created:

(DT_DBTIMESTAMP)(SUBSTRING(datum,1,4) + “-” + SUBSTRING(date,5,2) + “-” + SUBSTRING(date,7,2) + ” ” + SUBSTRING(time,1,2) + “:” + SUBSTRING(time,3,2) + “:” + SUBSTRING(time,5,2))

I hope they will create some more solid conversion functions in the future.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.