ssis convert dd/mm/yyyy yyyymmdd

Question

question

jhowe asked May 17, '12

ssis convert dd/mm/yyyy yyyymmdd

Hi title is pretty self explanatory! What's the best way of doing this in SSIS? I looked at data conversion but seems like it will only handle dd-mm-yyyy not with forward slashes?

sql-server-2005 ssis

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-05-17T14:26:04Z

jhowe answered May 17, '12

Yes that is pretty similar to what i came up with, whack this in a derived column transform TRIM(DateOfBirth) == "" ? NULL(DT_WSTR,1) : TRIM(SUBSTRING(DateOfBirth,7,4) + SUBSTRING(DateOfBirth,4,2) + SUBSTRING(DateOfBirth,1,2))

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2012-05-17T13:48:01Z

robbin answered May 17, '12

You can use SUBSTRING for that SUBSTRING(datetimecol, 7,4)+ SUBSTRING(datetimecol, 3,2) + SUBSTRING(datetimecol, 1,2)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question