Converting String values to a DATETIME datatype in SSIS expressions

By | April 8, 2009

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.