How to convert string in format yyyyMMdd to date using SSIS expression?

Question

I have a date 20130131 in csv which I'm trying to convert to 2013-13-01 using Derived Column in SSIS. I have used this expression but it does not seem to work.

(DT_DBTIMESTAMP)(SUBSTRING(DATE_DECISION_TO_REFER,1,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,3,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,5,2))

How do I rewrite this expression to produce the correct output with a value of data type DT_DBTIMESTAMP?

Answer 1

Fast Parse is a much more elegant solution

1. Add a Data Conversion Data Flow Component.
2. Right click, Show Advanced Editor...
3. Goto Input and Output Properties
4. Expand Data Conversion Output and click on the date column
5. Set FastParse to True in the Custom Properties
6. Make sure the data type is set to database date [DT_DBDATE]
7. Run the ssis package again the yyyymmdd dates should flow smoothly from nvarchar to date

Answer 2

As per Kyle Hale suggestion, below is my answer

    SUBSTRING([DATE_DECISION_TO_REFER],1,4) + "-" +
 SUBSTRING([DATE_DECISION_TO_REFER],5,2) + "-" + SUBSTRING([DATE_DECISION_TO_REFER],7,2)

To educate you so that you will never face this issue again, this is how expression works

 Get 4 characters from DATE_DECISION_TO_REFER starting at position 1, add a dash,
 get 2 characters from DATE_DECISION_TO_REFER starting at position 5,
 add a dash then add 2 characters from DATE_DECISION_TO_REFER starting at position 7.

Hope this will help.