OINV.createTS format hh:mm:ss

[former_member377478]

Hello,

please, could you help me with creating formatted search in OINV documents ? I would need to show OINV.CreateTS in format hh:mm:ss. Now it is only in format hhmmss. I´m able to convert it , but I cannot display the right value for only one OINV.DocNum.

SELECT STUFF(STUFF(STUFF(T0.CreateTS, 1, 0, REPLICATE('0', 6 - LEN(T0.createTS))),3,0,':'),6,0,':') from OINV T0

Thank you, regards

Martina

[wjmatamoros]

This query work for me in HANA:

SELECT TO_NVARCHAR(TO_TIME(LPAD(T0."CreateTS",6,0), 'HH24MISS')) AS FormattedTime
FROM OPCH T0;

[former_member377478]

oh my God,

I was really near the end :-).

Thank you, Johan,

regards

martina

[Johan_H]

Hi Martina,

Glad I could help.

Please close this question by selecting a best answer.

Regards,

Johan

[Johan_H]

Hi Martina,

You can use the formatted search parameter format:

select STUFF(STUFF(STUFF(T0.CreateTS, 1, 0, REPLICATE('0', 6 - LEN(T0.createTS))),3,0,':'),6,0,':') 
from OINV T0
where T0.DocNum = $[OINV.DocNum]

Regards,

Johan

[former_member686346]

This works for MS SQL can you share for HANA

[Johan_H]

Hi Amar,

HANA does not have the STUFF function, and possibly also not the REPLICATE function. Please check this post for an alternative to the STUFF function.

Regards,

Johan