extended-sql

Convert a number to time format HH:mm:ss

I am getting an integer in the input, for eg: 032614. I want to convert this into a time of format HH:mm:ss. For eg: 032614 should be converted to 03:26:14. Please let know if this can be done by ESQL

I have tried the below code but it fails with:

'Failed to match literal error'

CAST(cReqTimestamp AS TIMESTAMP FORMAT 'HH:mm:ss');
Solution

  • Below is a ESQL code snippet, target variable will have the output you need.

        DECLARE source INT 032614;
    DECLARE target CHARACTER;
    DECLARE pattern CHARACTER '00,00,00';
    SET target = REPLACE(CAST(source AS CHARACTER FORMAT pattern),',',':');

    You can go through the below link to get further information on CAST. https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak05610_.htm