How to convert string in format yyyyMMdd to date u

问题:

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?

回答1:

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.

回答2:

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

回答3:

Expression:

You should use the expression to format the date to yyyy-MM-dd like shown below. This expression will format the incoming value 20130131 to 2013-01-31 and then will convert to appropriate data type. The expression uses the incoming column named Column0.

(DT_DBTIMESTAMP)(SUBSTRING(Column0,1,4) + "-" + SUBSTRING(Column0,5,2) + "-" + SUBSTRING(Column0,7,2))

Issue:

Your expression is taking the value 20130131 and then incorrectly converting it to the value 20-13-01. This is an invalid format for dates and hence the expression is failing to type cast it to DT_DBTIMESTAMP data type.