How to use SUBSTRING with a FLOAT value?

Question

I need to take the first 8 values of a column to obtain the creation DATE. Then I need to take the remaining values for the TIME. I will then cast them as Date and Time for creating the EVENTTIME. The code below should have worked, but since it is a FLOAT data type I receive an error message on the Output.

Code:

SELECT ERSDA,ERSDA_TMSTP,SUBSTRING(ERSDA_TMSTP,0,8) AS "INFO1", SUBSTRING(ERSDA_TMSTP,9,4) AS "INFO2" FROM MCH1

Output:

Execution error: ERROR: Function catalog.substring(float, int, int) does not exist, or permission is denied for catalog.substring(float, int, int)

antonio.perez12 · Accepted Answer

Hi Salma, Have you tried casting to cast the float value to string? Something like this should work:SUBSTRING(CAST(ERSDA_TMSTP AS VARCHAR),0,8)Kind regards,

sunny.thind · Answer

I presume you are using substring in data integration transformation scripts. substring (a,b,c) function does work in transformation scripts. Have you tried on any other field other than ERSDA_TMSTP. If still an issue must be a localized issue and support call may be needed.

Hi Sunny, I tested it with other columns and it worked fine. Seems like it was a data type error and the solution was to CAST it as VARCHAR. Appreciate your input!

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN