How to convert decimal values to time values in oracle?

I have a table that is formatted like this:

date     | flag    |dec_time
04-MAY-21   2       0.54
04-MAY-21   5       2.18    
12-MAY-20   9       6.1
12-MAY-20   10      8
12-MAY-20   42      4
12-MAY-20   11      4.75


 

I want the output to be like this:

date     | flag    |new_dec_time
04-MAY-21   2       00:54
04-MAY-21   5       02:18   
12-MAY-20   9       06:10
12-MAY-20   10      08:00
12-MAY-20   42      04:00
12-MAY-20   11      04.75

Basically transform the values in the dec_time field into time values like the example above in new_dec_time.

This is my SQL so far:

select to_number(replace(dec_time,'.',':')) as new_dec_time

This simply changes replaces the decimal point with the semicolon but doesnt add the 0s where needed. Is there a simple way to go about doing this? Any ideas or suggestions would help.

Answer

Never rely on default NLS settings if you need exact format. Always specify NLS parameters explicitly (for example, decimal separator in Russian is comma, not period).

So this is the query, if your numbers are restricted to valid time. If not, you’ll get nonsense that looks like time. Also Oracle have no time datatype alone, that’s why you need to implement operations with strings by yourself.

with a as (
  select 2.18 as h from dual union
  select 0.54 from dual union
  select 6.1 from dual
)
select
  to_char(h, '00D00',
  'NLS_NUMERIC_CHARACTERS='': ''') as tm
from a

| TM     |
| :----- |
|  00:54 |
|  02:18 |
|  06:10 |

db<>fiddle here