Number to Time Oracle

Just wondering if I might be able to get some help. I'm trying to convert the following 42499 into a time field in Oracle. The field is currently a varchar2

Thank you

I've tried to_number(to_date(transactiontime, 'hh24ss'))

ORA-01850: hour must be between 0 and 23
01850. 00000 - "hour must be between 0 and 23"
*Cause:
*Action:

Oracle doesn't have a time-only data type, but you can convert to a date, as you are attempting; if the value you have represents a time as the number of seconds past midnight then you can use the sssss format model:

to_date(to_number(transactiontime), 'sssss')

Your client will probably be configured to show just the date, or the full date time, so you might see 01-MAY-23 or 2023-05-01 11:48:19 or something else. (If you don't specify any date elements it defaults to the first day of the current month.) You can convert the date back to a string with to_char():

to_char(to_date(to_number(transactiontime), 'sssss'), 'HH24:MI:SS')

which will give 11:48:19.

You can also convert your number to an interval data type with:

numtodsinterval(to_number(transactiontime), 'SECOND') 

or

to_number(transactiontime) * interval '1' second

which both give +000000000 11:48:19.000000000.

You can't format that directly, but you could cut out the bit you want with:

substr(to_number(transactiontime) * interval '1' second, 12, 8)

fiddle

Or you could add that interval to a fixed date (at midnight), such as a date literal like `date '1970-01-01'; or - if you are for some reason storing your transaction date and time as two separate fields (which is rarely, if ever, sensible) - to that date. And you can then format the combined value however you want.

For converting a number to date to simply need TO_DATE() function that will automatically convert it and also you can provide parameters for the output which you want.

Here is the complete guide of formats and syntax information Oracle Dates

You can use this query that will work for you. "Dual" is basically used when you want a dummy table(row/column) dataset and systems time etc.

Select TO_DATE('42499', 'HH:MM:SS') FROM dual;