Convert seconds to hour in calculated column

[former_member184624]

Dear HANA Experts,

There is a key figure WK_FDS with the data type "Number" which has a data "7,2000000000000000E+03" in BW System. (2 hours equal to 7200 seconds) In Bex Query designer, the function TIME(WK_FDS) is used and output is showing in the report as 02:00:00.

I want to achieve the same result in HANA. In HANA, the data type is DOUBLE for the field "WK_FDS". And i used the same function time() in calculated column. But i am getting the error message as "type error in expression evaluator;secondtime([here]time("WK_FDS"))(calculationNode (finalAggregation) -> attributes -> attribute (WY_CKF0052_Fitter_duration) -> expression)" Pls find the snap for your reference. How to rectify this error.

Thanks.

[Abhishek_Hazra · Accepted solution]

Hi jelina.masilamani

You need to define the calculated column in SQL engine as below :

to_varchar(add_seconds (to_timestamp ('2000-01-01 00:00:00'),"WK_FDS"),'hh:mi:ss')

The number you see in WK_FDS is quite likely in unit of seconds, you can't directly get timestamp out of it. FOr that you need to add the seconds with a timestamp which has initial hour minute & second value/ And then convert the timestamp output disregarding the irrelevant date part as you need it to be in HH:MI:SS format.

Best Regards,
Abhi

[former_member184624 · Accepted solution]

Thank you very much Abhi. I did the small change (Added HH24 in the code and i am getting the results.

to_varchar(add_seconds (to_timestamp ('2000-01-01 00:00:00'),"WK_FDS"),'hh24:mi:ss')