Im trying to convert a string field to date using TO_TIMESTAMP function in Celonis but receiving the error Undefined exception occured: ToTimestamp: Input does not fit to date format.
The function works well for a constant value.
e.g. TO_TIMESTAMP(20210101,YYYYMMDD)
but does not work for a table field. e.g. TO_TIMESTAMP(CEL_AR_ACTIVITIES.CHANGED_TO,YYYYMMDD) returns error.
Can someone help with the error?
Thanks.
Hi,
this error means that you have rows in your input column containing a string value which does not match the given date pattern. This can happen when, for example, you have strings in your input column where the string length does not match the given pattern, or the strings contain letters instead if numbers.
You can try to come up with a condition to remove those invalid strings from the input, e.g.
TO_TIMESTAMP (
CASE WHEN LEN("CEL_AR_ACTIVITIES"."CHANGED_TO") = 8
AND LEFT("CEL_AR_ACTIVITIES"."CHANGED_TO", 2) = '20'
THEN "CEL_AR_ACTIVITIES"."CHANGED_TO" ELSE NULL END
)This will only try to convert string values which have the correct length (=8 characters) and start with 20.
I assume you are on a rather old CPM4 on-prem version, right? In the EMS and in CPM4.6, TO_TIMESTAMP will not return this error anymore. Instead, a warning is given, and all values which cannot be converted are set to NULL.
Best
David
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.