## Forum Stats

• 3,826,359 Users
• 2,260,635 Discussions

Discussions

# Convert number to time

Member Posts: 18 Green Ribbon
edited Jun 22, 2022 12:00PM

Good morning,

I need some help converting a number to time.

For example, the value that comes out of SQL is: 7.5

This number needs to be converted to time: 07:30 how can I solve this?

another example is : 13.75 and this number should be converted to 13:45

Thanks in advance for the help :)

Tagged:

• Member Posts: 14 Red Ribbon
```select to_char(time'0:0:0'+numtodsinterval(7.5,'hour'),'hh24:mi:ss') tm from dual;

TM
--------
07:30:00
```
«1

• Member Posts: 1,942 Silver Crown
edited Jun 22, 2022 12:32PM

Here is one way:

```with hours(h) as (select * from table(sys.odcinumberlist(7.5,13.75)))
select h,(to_char(trunc(sysdate) + h/24,'HH24:MI:SS')) hhmm from hours

H HHMM
---------- --------
7.5 07:30:00
13.75 13:45:00
```

EDIT: You can also use any date literal like DATE '2022-01-01' instead of trunc(sysdate)

• Member Posts: 41 Red Ribbon
```SQL> ed
Wrote file afiedt.buf

1  with t as (
2  select 7.5 n from dual union all
3  select 13.75  from dual union all
4  select 20.333  from dual union all
5  select 2.00  from dual union all
6  select 0.25  from dual union all
7  select 25.3 from dual
8  )
9* select n,trunc(n) h,round(60*mod(n,1)) mi from t
SQL> /

N          H         MI
---------- ---------- ----------
7.5          7         30
13.75         13         45
20.333         20         20
2          2          0
.25          0         15
25.3         25         18

6 rows selected.

```
• Member Posts: 19,471 Red Diamond

@cormaco: Here is one way

It will only work if number < 24. Otherwise:

```with hours(h) as (select * from table(sys.odcinumberlist(7.5,13.75,33.25)))
select h,(to_char(trunc(sysdate) + h/24,'HH24:MI:SS')) hhmm from hours
/

H HHMM
---------- --------
7.5 07:30:00
13.75 13:45:00
33.25 09:15:00 -- wrong

SQL>
```

SY.

• Member Posts: 14 Red Ribbon
```select to_char(time'0:0:0'+numtodsinterval(7.5,'hour'),'hh24:mi:ss') tm from dual;

TM
--------
07:30:00
```
• Member Posts: 1,942 Silver Crown
```time'0:0:0'
```

The time literal is an undocumented feature, but date '1-1-1' works as well.

• Member Posts: 19,471 Red Diamond

First of all, type TIME is undocumented. Secondly, same as solution from cormaco it will not work if number >= 24:

```SQL> select to_char(time'0:0:0'+numtodsinterval(39.25,'hour'),'hh24:mi:ss') tm from dual;

TM
--------
15:15:00

SQL>
```

SY.

• Member Posts: 41 Red Ribbon
```SQL> select numtodsinterval(39.25,'hour') from dual;

NUMTODSINTERVAL(39.25,'HOUR')
---------------------------------------------------------------------------
+000000001 15:15:00.000000000
```
• Member Posts: 19,471 Red Diamond

@Stax: This displays it as days and hours while OP, if I got it right, wants total hours: 39:15.

SY.

• Member Posts: 10,539 Blue Diamond

Here is one way. The formula uses a trivial algebraic simplification of the following approach: keep the integer part as the number of hours; multiply the fractional part by 0.6 (60/100). Then format the result using colon as the decimal point, to get the result in the string format `hh:mi`.

```with
inputs (decimal_hours) as (
select   7.5  from dual union all
select   4.25 from dual union all
select 140    from dual union all
select   0.05 from dual union all
select   2.47 from dual
)
select decimal_hours,
to_char(0.6 * decimal_hours + 0.4 * trunc(decimal_hours), 'fm99999990d00',
'nls_numeric_characters=:.') as hh_mi
from   inputs;

DECIMAL_HOURS   HH_MI
-------------   ------------
7.5   7:30
4.25   4:15
140   140:00
.05   0:03
2.47   2:28

```

Note the rounding applied to the last sample input. 0.47 hours is not a whole number in minutes, it's 28.2 minutes. If you don't want that result shown as 2;28 (2 hours and 28 minutes), since that loses precision, indicate what else you want as output. Perhaps 2:28.2 or 2:28:24 (in `hh:mi:ss` format) - understanding that even the seconds may not always be a whole number, so there may still be some rounding even then.

• Member Posts: 18 Green Ribbon

Thank you all for your help! We have solved the problem through your help.