SSIS date conversion cheatsheet

I seriously can’t remember the number of times that I have had to lookup date conversion logic in SSIS. Decided to create a cheatsheet for it.

Date in Flat FileSQL Data TypeData Conversion TransformationDerived Column Transformation
yyyymmdddateDT_DBDATE (FastParse = True)
yyyy-mm-dddateDT_DBDATE (optional)
dd/mm/yyyydateDT_DBDATE (optional)
mm/dd/yyyydate(DT_DBDATE)(SUBSTRING([Col],7,4) + "-" + SUBSTRING([Col],1,2) + "-" + SUBSTRING([Col],4,2))
yyyy-mm-dd hh:mm:ssdatetimeDT_DBTIMESTAMP (optional)
yyyy-mm-dd hh:mm:ss.nnnnnnndatetime2DT_DBTIMESTAMP2 (scale = count(n)) (optional)

yyyymmdd

Most solutions on stackoverflow would suggest that you use a derived column transformation to insert dashes to form a yyyy-mm-dd string and then cast it to DT_DBDATE. A much simpler way is to turn on a custom property called FastParse. (Thanks to my co-worker Lynn for passing on this tip)

FastParse can be found in Show Advanced Editor -> Input and Output Properties -> Data Conversion Output -> Output Columns

yyyy-mm-dd

This is the simplest format to handle. A data conversion transformation will do. It can be implicitly loaded into a date column as is.

dd/mm/yyyy

This is locale-specific so a data conversion transformation can easily handle it it can be implicitly loaded given that we are in Australia.

mm/dd/yyyy

I have to say this is the most hated date format in the database world (this part of the world at least). Now that we are in Australia and it is a US date format, it cannot be converted directly using a data conversion transformation. Instead we need to rely on a derived column transformation to pull apart the date components and reconstruct into the yyyy-mm-dd format.

EDIT: Actually I later found that SSIS can handle both the dd/mm/yyyy and mm/dd/yyyy format as long as the days are larger than 12, even for dates with mixed format within the same column. Will write about this in a future post.

yyyy-mm-dd hh:mm:ss

This is a perfect date time format that can be converted using a data conversion transformation. Just need to remember to use the DT_DBTIMESTAMP data type. Or, simply load into a datetime column as is.

yyyy-mm-dd hh:mm:ss.nnnnnnn

This is the format for a SQL datetime2 data type. In order to do a direct data conversion, the scale needs to match the digits in the fractional seconds. Basically, no fractional seconds = no scale, 1 digit  = scale of 1, 2 digits = scale of 2, and so on up to 7. Again, simply load into a datetime2 column as is.