String to Date Conversion in TI - "S1000[Oracle][ODBC][Ora]ORA-01861: literal does not match format string"

[AlphaWay]

Hi All,

My Data flow
DB -> TM1 -> DB
Date - > String -> Date

Issue Format : 2021-12-31

In the final "Cube view to Table load" process , I have a insert statement in which i am sending Date column without conversion , which throws an following error .

"S1000[Oracle][ODBC][Ora]ORA-01861: literal does not match format string" -- it says Date format is wrong in my insert statement .

I extracted the insert query and tested in Oracle, the cause is the date(Issue format) . I modified the format to "01-Jan-2021" and it worked in oracle .

How do i change the format in TI process ?

[declanr]

Generally speaking I would suggest if possible that you store the date as a numeric cell in TM1 and just format it to look like a date; generally that makes most things a lot easier.

That being said since you currently have it as a string; I would start by converting your string to a number ( ParseDate function ) and then with that serial number you can convert it into any format of date you want using the FormatDate function.

[Wim Gielis]

For information about NewDateFormatter, ParseDate, FormatDate: see here.

[scrumthing]

For information around NewDateFormatter, ParseDate, FormatDate: see here.

Thanks for that Wim. Good read!