Getting error while converting a string to datetime in derived column

Question

I have a column named Paid_Date in my table that has a kind of improper date value with a colon in between date and the time values (e.g. '04MAY2015:00:00:00').

In order to exclude that colon, I have used an expression

((DT_DBTIMESTAMP)SUBSTRING(PAID_DATE,1,9))

in my Derived Column transformation.

When I try running this, I'm getting the following error.

Expression used in my trasformation: (DT_DBTIMESTAMP)SUBSTRING(PAID_DATE,1,9)
Source column data type : varchar             
Source column Value: 04MAY2015:00:00:00
Error: [Derived Column [1613]] Error: An error occurred while attempting to perform a type cast.
Detailed Error:[Derived Column [1613]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  
The "component "Derived Column" (1613)" failed because error code 0xC0049064 occurred, 
and the error row disposition on "output column "PaidDate" (1954)" specifies failure on error. 
An error occurred on the specified object of the specified component.  
There may be error messages posted before this with more information about the failure.

I have even tried to use the following expression:

(DT_DBTIMESTAMP)((DT_STR,50,1252)SUBSTRING(PAID_DATE,1,9))

Is it not possible to convert a string after a SUBSTRING operation?

Answer 1

Casting a string to a DT_DBDATETIME must use a specific string format:

yyyy-mm-dd hh:mm:ss[.fff]

You'll need to transform the string to the standard format before the cast will work.

Extract the month, use a lookup transformation or nested ternary operators to change it to two digit numeric string, then format the results into a string you can cast into a DT_DBDATETIME.