How can I convert string (YYMMDD) to datetime using derived column transformation?

Question

I have an input text file, which consists of few columns namely TransactionID, receiveddt, description etc. The recieveddt column has date value in the following format 120419 (yymmdd). I would like to load the .txt input file into a database but the destination column recieveddt is of data type DateTime. I used the derived column transformation to convert the incoming receiveddt column value to datetime as shown below

Derived Column Name  Derived Column     Expression            Data Type 
-------------------  -----------------  --------------------  ---------
receiveddate         replace reciveddt  (DT_DATE)[reciveddt]  datetime 

I tried different combinations but I keep gettings errors while attempting to perform a type cast.

How can I convert string (YYMMDD) to datetime using derived column transformation?

Answer 1

Expression:

(DT_DATE)("20" + SUBSTRING([ReceivedDt], 1, 2) + "-" + SUBSTRING([ReceivedDt], 3, 2) + "-" + SUBSTRING([ReceivedDt], 5, 2))

For readability:

(DT_DATE)("20" + 
    SUBSTRING([ReceivedDt], 1, 2)  + "-" + 
    SUBSTRING([ReceivedDt], 3, 2)  + "-" +
    SUBSTRING([ReceivedDt], 5, 2))

Cause of the issue:

You cannot convert string in format YYMMDD to valid date value. Use the above expression to prefix the value with 20 to convert the values to format YYYYMMDD that could be converted to dates.

SSIS 2012 package that illustrates the above given expression:

Configure the Data Flow Task with a OLE DB Source, Derived Column Transformation and two Multicast Transformations.

Configure the OLE DB Source with the following query that has the values in format YYMMDD.

SELECT  '120304' AS ReceivedDt UNION
SELECT  '120107' AS ReceivedDt UNION
SELECT  '121211' AS ReceivedDt UNION
SELECT  '121312' AS ReceivedDt;

Configure the Derived Column Transformation to convert the incoming value in column ReceivedDt in format YYMMDD to ReceivedDate of format YYYYMMDD.

Attach data viewer between the Derived Column Transformation and the Multicast Transformations. Configure the Error Output to Redirect row instead of Fail component.

Executing the package will display the string values that could be converted to valid date values in the data viewer between Derived Column Transformation and the success Multicast Transformation.

Executing the package will display the string values that could not be converted to in the data viewer between Derived Column Transformation and the error Multicast Transformation.

Answer 2

Try this:

(DT_DATE)("20" + SUBSTRING(receivedt,1,2) + "-" + SUBSTRING(receivedt,3,2) + "-" + SUBSTRING(receivedt,5,2))

Note that this conversion assumes that all dates are in the current century. If you have dates in the previous century, you should add a condition to check for that.

Also define an error path for your derived column transformation to catch misformatted dates. You can then, for example, give them a default value.