20

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?

Djbril
  • 745
  • 6
  • 26
  • 48

3 Answers3

26

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
Louie Bao
  • 1,632
  • 2
  • 16
  • 23
  • 6
    there's a step between 1 and 2. Open normally and add the column with the text that needs to be converted and name the output column. Then Close and continue to step 2. – KeithL Jun 14 '17 at 14:39
18

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, 
 get 2 characters from DATE_DECISION_TO_REFER starting at position 7.

Hope this will help.

Marcucciboy2
  • 3,156
  • 3
  • 20
  • 38
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • Thank You everyone!!! Kyle thanks for your answer and especially for educating me much appreciated. – Djbril Mar 08 '13 at 23:45
  • thank you for this - another forum had SUBSTRING using a 0 based index which would not parse.. I should have checked SO first. – Adam Mar 16 '15 at 16:22
11

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.

Community
  • 1
  • 1