Need to convert Hrs(Number) to HH:mm oracle syntax

I have a  column Total NO:of eng HRS in Number format in oracle database and I need output of this field as HH:MM.

Please help me with oracle syntax to convert

Thanks In Advance
Sunshine007Asked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Based on your question you are looking for Oracle syntax, so this should work.

This demonstrates how to pull apart the number of hours into hours, minutes and seconds.
WITH tab1 
     AS (SELECT 12.459 hrs 
         FROM   dual) 
SELECT Floor(hrs)                       hours, 
       Floor(( hrs - Floor(hrs) ) * 60) mins, 
       Floor(( hrs - Floor(hrs) - ( Floor(( hrs - Floor(hrs) ) * 60) / 60 ) ) * 
             3600) 
                                        secs 
FROM   tab1; 

Open in new window

From that, if your table has a column that is named HRS, then to put it together into the format you want it would be:
WITH tab1 
     AS (SELECT 12.459 hrs 
         FROM   dual) 
SELECT Floor(hrs) 
       || ':' 
       || Floor(( hrs - Floor(hrs) ) * 60) 
       || ':' 
       || Floor(( hrs - Floor(hrs) - ( Floor(( hrs - Floor(hrs) ) * 60) / 60 ) ) 
                * 3600 
          ) hhmmss 
FROM   tab1; 

Open in new window

0
 
Mark ElySenior Coldfusion DeveloperCommented:
(DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(m, @yourValue) * 60) + DATEPART(s, @yourValue)
0
 
Sunshine007Author Commented:
I think This converts seconds to hours and minutes. But my field gives Hrs
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Sunshine007Author Commented:
So I need to convert hrs(which is in number datatype) to HH:MM:SS
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Try this

DECLARE @Val1 int
DECLARE @Val2 int
DECLARE @Val3 int

SET @Val1 = 12
SET @Val2 = 40
SET @Val3 = 30

SELECT CAST(@val1 as varchar(2)) + ':' + cast(@val2 as varchar(2)) + ':' + cast(@val3 as varchar(2))
0
 
Mark ElySenior Coldfusion DeveloperCommented:
The declare and set values can be removed.  I placed them there for temp output
0
 
sdstuberCommented:
select   to_char(trunc(sysdate) + hours/24,'hh:mi')  from your_table

or if you want 24 hour format

select   to_char(trunc(sysdate) + hours/24,'hh24:mi')  from your_table


some examples....

WITH sample_data
     AS (SELECT 1 hrs FROM DUAL
         UNION ALL
         SELECT 2.3 FROM DUAL
         UNION ALL
         SELECT 3.45 FROM DUAL
         UNION ALL
         SELECT 12.345 FROM DUAL
         UNION ALL
         SELECT 18.5 FROM DUAL)
SELECT hrs, TO_CHAR(TRUNC(SYSDATE) + hrs / 24, 'hh24:mi')
  FROM sample_data;

 1      01:00
 2.3    02:18
 3.45   03:27
 12.345 12:20
 18.5   18:30

Open in new window



note, any truncated date value is sufficient.  So you could use an ansi literal if you wanted instead of trunc(sysdate)

such as date '2000-01-01'

select TO_CHAR(date '2000-01-01' + hrs / 24, 'hh24:mi') from your_table
0
 
johnsoneSenior Oracle DBACommented:
Thanks sdstuber.  I knew there was an easier way to do it, but I couldn't think of it.
0
 
sdstuberCommented:
mine does assume the result will be 00:00 - 23:59.

If the values will be larger than that, then you must do the math yourself, or use intervals.

 TRUNC(hrs) || ':' || EXTRACT(MINUTE FROM NUMTODSINTERVAL(hrs, 'hour'))

if by chance you'll have negative hours then try this...

 TRUNC(hrs) || ':' || ABS(EXTRACT(MINUTE FROM NUMTODSINTERVAL(hrs, 'hour')))
0
 
Sunshine007Author Commented:
Thank You for your help I will try this
0
 
sdstuberCommented:
you wanted to use the most complicated of all of the options?

I'm not suggesting it won't work, just surprised that you'd prefer it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.