cancel
Showing results for 
Search instead for 
Did you mean: 

Data type Signavio PI

santiagolc
Explorer
0 Kudos

Hello,

I am working with Signavio PI, I have used the manual upload module to upload P2P data. I have configured the date and time fields as STRING. Now I want to run the transformation but I have some errors about the data type. For example I need to use CDHDR.UDATE with CDHDR.UTIME with this syntax:

CAST(cdhdr.udate ||' '|| cdhdr.utime AS TIMESTAMP) as c_time

But Signavio throws an error message about: INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp: 2022-02-11 16:22:180012383936

If I search both fields individually I can see both fine without any problem and the time field only shows for example 16:22:18 (without these numbers 001238383936)

I have used this syntax with other date and time fields and it works perfect. does anyone know another syntax to use? to above this problem?

Thanks, best regards.

Accepted Solutions (1)

Accepted Solutions (1)

fabioferrari
Advisor
Advisor

Hi Santiago, here a few hints how you could play with dates and times in prestoSQL. If it does not help, I would recommend creating a ticket! Hope it helps 😉
Fabio

santiagolc
Explorer
0 Kudos

Thanks for your help! The code works perfect!

Best regards.

Answers (1)

Answers (1)

fabioferrari
Advisor
Advisor

Hi Santiago,

our Process Intelligence solution uses Presto SQL language in Process Data Management. Here you can have access to the documentation to check your syntax: https://prestodb.io/docs/current/

Here it is shared a similar error you are facing: https://stackoverflow.com/questions/59902418/geeting-invalid-cast-argument-error-in-athena-presto . I hope it can help!

Fabio

santiagolc
Explorer
0 Kudos

Hi Fabio,

Thanks for your reply. I was looking at that page but I didn't know how to use these functions. Regarding the link you gave me I tried that solution but I still have problems, I think it's because the data comes to me in two different ways. I'll show you the errors that I get according to the syntax I use:

-- Original code
SELECT date_parse(UDATE ||' '|| UTIME, '%Y-%m-%d %H:%i:%s.%f') AS fecha FROM CDHDR

Error: PREVIEW_FAILED Error Id: 0110bac4-3729-4318-8e00-5899454aea64 INVALID_FUNCTION_ARGUMENT: Invalid format: "2022-01-28 14:55:58" is too short

-- without .%f
SELECT date_parse(UDATE ||' '|| UTIME, '%Y-%m-%d %H:%i:%s') AS fecha FROM CDHDR

Error: PREVIEW_FAILED Error Id: fe2aac48-b4ac-47db-96ee-12c388ed3970
INVALID_FUNCTION_ARGUMENT: Invalid format: "2022-02-11 16:22:180012383936" is malformed at "0012383936"