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

(DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(m, @yourValue) * 60) + DATEPART(s, @yourValue)

I think This converts seconds to hours and minutes. But my field gives Hrs

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

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

The declare and set values can be removed.  I placed them there for temp output

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

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

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

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

Thank You for your help I will try this

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.