8 Replies Latest reply on Mar 2, 2009 9:15 AM by 618957

# Oracle 10g - Convert decimal to HH:MM

Hi

How can I convert a decimal to Hours:Minutes format eg. 2.75 to 2 Hours and 45 Minutes
• ###### 1. Re: Oracle 10g - Convert decimal to HH:MM
Hi!
``SELECT NUMTODSINTERVAL(2.75, 'HOUR') FROM dual;``
yours sincerely

Florian W.
• ###### 2. Re: Oracle 10g - Convert decimal to HH:MM
Hi

Had a look at that function before but I do not want to show it in that format. The ideal format would be 129hrs:39mins:25secs
Regards
• ###### 3. Re: Oracle 10g - Convert decimal to HH:MM
how do you get from 2.75 to 129hrs:39mins:25secs?

Florian gave you exactly what you initially asked. Maybe it's time to specify exactly what you're looking for...
• ###### 4. Re: Oracle 10g - Convert decimal to HH:MM
If I have a decimal 129.65, would'nt NUMTODSINTERVAL be a problem. I would like to show 2.75 as 2hrs45min or
129.657 as 129hrs:39mins:25secs
Regards
• ###### 5. Re: Oracle 10g - Convert decimal to HH:MM
Hi once again!

Are you looking for this?
``````SELECT SUBSTR(numtodsinterval(2.75, 'HOUR'), 12, 2) || 'hrs:' ||
SUBSTR(numtodsinterval(2.75, 'HOUR'), 15,2) || 'mins:' ||
SUBSTR(numtodsinterval(2.75, 'HOUR'), 18,2) || 'secs' FROM dual;``````
regards
• ###### 6. Re: Oracle 10g - Convert decimal to HH:MM
or...
``````SELECT extract (hour from numtodsinterval(2.75, 'HOUR' ))|| 'hrs:' ||
extract (minute from numtodsinterval(2.75, 'HOUR' )) || 'mins:' ||
extract (second from numtodsinterval(2.75, 'HOUR')) || 'secs'
FROM dual;``````
• ###### 7. Re: Oracle 10g - Convert decimal to HH:MM
or perhaps
``````SQL> SELECT TRUNC(2.75) || 'hrs:' ||
SUBSTR(numtodsinterval(2.75, 'HOUR'), 15,2) || 'mins:' ||
SUBSTR(numtodsinterval(2.75, 'HOUR'), 18,2) || 'secs' FROM dual

TRUNC(2.75)||'HRS:
------------------
2hrs:45mins:00secs
1 row selected.
SQL> SELECT TRUNC(129.657) || 'hrs:' ||
SUBSTR(numtodsinterval(129.657, 'HOUR'), 15,2) || 'mins:' ||
SUBSTR(numtodsinterval(129.657, 'HOUR'), 18,2) || 'secs' FROM dual

TRUNC(129.657)||'HRS
--------------------
129hrs:39mins:25secs
1 row selected.``````
Or maybe better
``````SQL> SELECT TRUNC(2.75) || 'hrs:' ||
extract (minute from numtodsinterval(2.75, 'HOUR' )) || 'mins:' ||
extract (second from numtodsinterval(2.75, 'HOUR')) || 'secs'
FROM dual

TRUNC(2.75)||'HRS:'||EXTRACT(MINUTEFROMNUMTODSINTERVAL(2.75,'HOUR'))||'MINS:'||E
--------------------------------------------------------------------------------
2hrs:45mins:0secs
1 row selected.
SQL> SELECT TRUNC(129.657) || 'hrs:' ||
extract (minute from numtodsinterval(129.657, 'HOUR' )) || 'mins:' ||
extract (second from numtodsinterval(129.657, 'HOUR')) || 'secs'
FROM dual

TRUNC(129.657)||'HRS:'||EXTRACT(MINUTEFROMNUMTODSINTERVAL(129.657,'HOUR'))||'MIN
--------------------------------------------------------------------------------
129hrs:39mins:25,2secs
1 row selected.``````
Regards
Peter

Edited by: Peter on Mar 2, 2009 12:52 AM
• ###### 8. Re: Oracle 10g - Convert decimal to HH:MM
Thanks for your responses and I apologize if I did not make myself clear from the beginning.

I failed to include trunc in my statement and Peter's reply is what I needed which also caters for the large decimal

Thanks once again