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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Sunshine007Author Commented:
So I need to convert hrs(which is in number datatype) to HH:MM:SS
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.