Format a JD Edwards number column as HH24:MI:SS

Question

I have a JD Edwards table column with the following value 170443 which represents time. I want to format it as HH:MI:SS but I'm getting an error ORA-01850: hour must be between 0 and 23. What changes do I need to apply to the following code to get the output to appear as 17:04:43?

SELECT CASE WHEN LVUPMT= 0 THEN NULL 
  ELSE
    TO_TIMESTAMP(substr(LVUPMT,1,2) ||':'||substr(LVUPMT,3,2) ||':'||substr(LVUPMT,5,2), 'HH24:MI:SS')
  END AS DDATE 
FROM PRODDTA.F07620 WHERE LVAN8 = 102366;

Answer 1

Cast it to a date using a mask which fits the format of your data then cast it to a tsring to get the format your want:

SELECT CASE WHEN LVUPMT= 0 THEN NULL 
  ELSE
    to_char( to_date(LVUPMT, 'HH24MISS'), 'HH24:MI:SS')
  END AS DDATE 
FROM PRODDTA.F07620 WHERE LVAN8 = 102366;