SSIS convert string to date

Question

I am new to SSIS, have been trying to convert the string to date. My input column string is in the format of MM.DD.yy 10.11.16 My requirement is to convert 2016-10-11 yyyy-mm-dd

Below is the expression I tried but its loading as null in the destination.

(DT_DATE)(SUBSTRING(FILE_DATE,1,2) + "-" + 
SUBSTRING(FILE_DATE,4,2) + "-" + SUBSTRING(FILE_DATE,7,2))

The expression you have correctly returns a DATETIME value. Is your destination column / variable that needs to be transformed to yyyy-mm-dd a string data type? — Shiva
What data type is the target column? What do you see if you add a data viewer in your package? — Nick.McDermaid

Yahfoufi Yahfoufi · Accepted Answer · 2016-11-04T07:07:31

First Method

in the DataFlowTask add a script component between Source and Destination

Check The FILE_DATE as Input And Create A column outFILE_DATE (Type: DT_DATE) as Output

in the Script Window add the Following Code

Row.outFILEDATE = DATETIME.ParseExact(Row.FILEDATE,"MM.DD.yy",New System.Globalization.CultureInfo("En-GB"))

And Map the outFILE_DATE to the Destination Column

Second Method

Use this expression :

(DT_DATE)("20" + SUBSTRING(FILE_DATE,7,2) + "-" + SUBSTRING(FILE_DATE,4,2) + "-" + SUBSTRING(FILE_DATE,1,2) )

it works only when Year is >= 2000

SSIS convert string to date

1 Answers