CODEWITHSUNDEEP
informaticainformatica-powercenterinformatica-cloudiicsiics-di
Lan
Lan

Reputation: 71

Informatica Cloud Expression Transformation Convert UTC String to DateTime

I want to convert the UTC datetime in string format to datetime format:

"2016-05-12T21:19:51.2946214Z" -> 2016-05-12 21:19:51.294

I tried to first remove the letters in the original string by using the SUBSTR() function, and then convert the modified string to datetime format: "2016-05-12T21:19:51.2946214Z" -> "2016-05-12 21:19:51.294" -> 2016-05-12 21:19:51.294

However, when I run the expression I found this error:

Transformation [Expression] had an error evaluating output column [Out_CreationDate]. Error message is [<<Expression Error>> [TO_DATE]: invalid string for converting to Date
... t:TO_DATE(u:'2016-05-12 21:19:51.29',u:'MM/DD/YYYY HH24:MI:SS')].

This error is saying the string is invalid for converting to Date, do you know why this string is not working? What kind of string should I change it to to make the TO_DATE() function work?

Thank you!

I am expecting an expression transformation that can convert UTC time in string format to datetime format

Upvotes: 1

Views: 1176

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

you need to use to_date() to convert to a date.

to_date('2008-03-01 07:42:12','yyyy-mm-dd hh:mi:ss')

i assume you removed the millisecond part and replaced T with ' '.

Upvotes: 1

Related Questions