Ensure your "upmt" column is 6 digits before trying to format it.
Here's an example with the address book(F0101).
SELECT abupmt,
TO_TIMESTAMP(substr(abupmt,1,2) ||':'||substr(abupmt,3,2) ||':'||substr(abupmt,5,2), 'HH24:MI:SS')
FROM PRODDTA.f0101
WHERE 1=1
and abupmt < 100000;
ORA-01850: hour must be between 0 and 23
01850. 00000 - "hour must be between 0 and 23"
*Cause:
*Action:
/* Now ensure time column is 6 digits */
SELECT abupmt,
substr(lpad(abupmt,6,'0'),1,2) ||':'||substr(lpad(abupmt,6,'0'),3,2) ||':'||substr(lpad(abupmt,6,'0'),5,2) AS jde_just_formatted,
TO_TIMESTAMP(substr(lpad(abupmt,6,'0'),1,2) ||':'||substr(lpad(abupmt,6,'0'),3,2) ||':'||substr(lpad(abupmt,6,'0'),5,2), 'HH24:MI:SS') AS jde_timestamp
FROM PRODDTA.f0101
WHERE 1=1
AND abupmt < 100000
AND rownum < 4;
ABUPMT JDE_JUST_FORMATTED JDE_TIMESTAMP
---------- -------------------- -----------------------------------
22103 02:21:03 01-MAY-19 02.21.03.000000000 AM
22336 02:23:36 01-MAY-19 02.23.36.000000000 AM
92342 09:23:42 01-MAY-19 09.23.42.000000000 AM
3 rows selected.