to_date function

[sa0309]

I'm trying to convert a string [ADM_DATETIME_I] ( 2012-08-04 2:38:00 AM ) to a date in an expression.

TO_DATE(ADM_DATETIME_I,'yyyy-mm-dd HH:MI:SS AM')

Getting the following 2 errors:

TE_7007 Transformation Evaluation Error [<<Expression Error>> [TO_DATE]: invalid string for converting to Date
... t:TO_DATE(u:'2012-08-04 2:38:00 AM',u:'yyyy-mm-dd HH:MI:SS AM')]

TT_11132 Transformation [EXP_TSI_DETAILS] had an error evaluating output column [ADM_DATETIME_O]. Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date
... t:TO_DATE(u:'2012-08-04 2:38:00 AM',u:'yyyy-mm-dd HH:MI:SS AM')].

Any assistance / suggestions would be appreciated.

Thank you.

 

[sa0309]

I determined the issues was caused by the incoming data format. After looking at [ADM_DATETIME_I] further, it's coming in either of the 2 following formats:

a) 2012-08-04 2:38:00 AM
b) 2012-08-04

Is it possible in a expression to check for "either" format and if no time exists, default to YYYY-MM-DD 00:00:00 AM?

Thank you.

 

[rasETL]

Check out the "length" Transformation function.

You can process the column differently based on length...

IIF(Length(in_ADM_DATETIME)= 10