SSIS convert date in mmddyyyy format to date format using SSIS expression

Question

I'm receiving dates in a MMddyyyy format but want to convert it to the date format in SQL/SSIS yyyy-MM-dd, how can I do so using an SSIS expression.

An example of dates I'm receiving is 03051978 but want it to appear in 1978-03-05.

Thank you

Answer 1

You can use the following expression:

(DT_DATE)(RIGHT("0000" + RIGHT("03051978",4),4) + "-" + RIGHT("00" + LEFT("03051978",2),2) + "-" + RIGHT("00" + SUBSTRING("03051978",3,2),2))

Just replace "03051978" with the column name, as an example:

(DT_DATE)(RIGHT("0000" + RIGHT([DateColumn],4),4) + "-" + RIGHT("00" + LEFT([DateColumn],2),2) + "-" + RIGHT("00" + SUBSTRING([DateColumn],3,2),2))

Screenshot from SSIS expression tester tool