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.

Who is Participating?

Senior 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;
``````
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;
``````
0

Senior Coldfusion DeveloperCommented:
(DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(m, @yourValue) * 60) + DATEPART(s, @yourValue)
0

Author Commented:
I think This converts seconds to hours and minutes. But my field gives Hrs
0

Author Commented:
So I need to convert hrs(which is in number datatype) to HH:MM:SS
0

Senior 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

Senior Coldfusion DeveloperCommented:
The declare and set values can be removed.  I placed them there for temp output
0

Commented:
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
``````

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

Senior Oracle DBACommented:
Thanks sdstuber.  I knew there was an easier way to do it, but I couldn't think of it.
0

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

Author Commented:
Thank You for your help I will try this
0

Commented:
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.