1

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

La. Li
  • 41
  • 6
  • I changed the string to this format "2008-03-01 07:42:12" (like I deleted those milisecond data) Because I noticed informatica is saying that its default format is only up to seconds HH24:MI:SS. However, still got the same error : Transformation Evaluation Error [<> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(u:'2008-03-01 07:42:12',u:'MM/DD/YYYY HH24:MI:SS')] – La. Li Apr 26 '23 at 19:25

1 Answers1

1

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 ' '.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thank you this answer worked, what I was doing before is to_date('2008-03-01 07:42:12'), I didn't specify the format yyyy-mm-dd hh:mi:ss because I assume by default it should be this one. And that did not work, but anyways adding this back solved the problem. Thank you so much to help a newbie like me! – La. Li Apr 27 '23 at 12:37
  • Glad it helped! infa assume default format as `MM/DD/YYYY HH24:MI:SS`. Your data is in different format. – Koushik Roy Apr 27 '23 at 14:10
  • Oh I see! it is because the '/' sign, Thank you for your further explanation! – La. Li Apr 27 '23 at 17:34