8 Replies Latest reply: Mar 2, 2009 3:15 AM by 618957 RSS

    Oracle 10g - Convert decimal to HH:MM

    618957
      Hi

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

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

            Thanks for your response

            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
              21205
              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
                618957
                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
                  FlorianW.
                  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
                    21205
                    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
                      Peter Gjelstrup
                      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
                        618957
                        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