8 Replies Latest reply: Jan 1, 2013 8:06 AM by JohnWatson

# I want to convert number to hours in oracle 10 like this.

Dear All

Some can help me

I want to convert below MS-SQL query in oracle 10g. please help me.

for eg:

Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable

Output:

9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes

Edited by: Parwez on Jan 1, 2013 5:33 AM
• ###### 1. Re: I want to convert number to hours in oracle 10 like this.
What code have you tried so far?
• ###### 3. Re: I want to convert number to hours in oracle 10 like this.
I want to convert number to hours in oracle 10g like this.

537 8 hours 57 minutes
9436 157 hours 16 minutes
12525 208 hours 45 minutes
Try this...
``````Ranit>> with xx as(
2      SELECT 537 num from dual UNION ALL
3       SELECT 9436 num from dual UNION ALL
4       SELECT 12525 num from dual
5  )
6  select
7       num,
8       FLOOR(num/60)||' hrs' as "hrs",
9       MOD(num,60)||' minutes' as "minutes",
10       FLOOR(num/60)||' hrs  '||MOD(num,60)||' minutes' as "Hours.Mins"
11  from xx;

NUM hrs                                          minutes                                          Hours.Mins
---------- -------------------------------------------- ------------------------------------------------ ----------------------------------------------------------------------------------------------
537 8 hrs                                        57 minutes                                       8 hrs  57 minutes
9436 157 hrs                                      16 minutes                                       157 hrs  16 minutes
12525 208 hrs                                      45 minutes                                       208 hrs  45 minutes                                                                                                ``````
Edited by: ranit B on Jan 1, 2013 6:51 PM
--- Hey John... I just did it and saw your hint now !!!
• ###### 4. Re: I want to convert number to hours in oracle 10 like this.
Well OK, but I'ld do it in a function:
`````` 10* end mins_2_hours;
orcl> /

Function created.

orcl> select mins_2_hours(537) from dual;

MINS_2_HOURS(537)
--------------------------------------------
------------------
8 hours 57 minutes

orcl> select mins_2_hours(9436) from dual;

MINS_2_HOURS(9436)
--------------------------------------------
------------------
157 hours 16 minutes

orcl> select mins_2_hours(12525) from dual;

MINS_2_HOURS(12525)
--------------------------------------------
------------------
208 hours 45 minutes

orcl>``````
Hint look at using MOD()
• ###### 5. Re: I want to convert number to hours in oracle 10 like this.
And why are you using FLOOR and not TRUNC? OP didn't mention numbers are non-negative, right? So why provide solution that works only for non-negative numbers?
``````SQL> with t as(
2            SELECT -537 num from dual UNION ALL
3       SELECT 9436 num from dual UNION ALL
4       SELECT 12525 num from dual
5           )
6  select  trunc(num/60) || ' hours ' || abs(mod(num,60)) || ' minutes'
7    from  t
8  /

TRUNC(NUM/60)||'HOURS'||MOD(NUM,60)||'MINUTES'
----------------------------------------------------------------------------

-8 hours 57 minutes
157 hours 16 minutes
208 hours 45 minutes

SQL> with xx as(
2      SELECT -537 num from dual UNION ALL
3     SELECT 9436 num from dual UNION ALL
4     SELECT 12525 num from dual
5  )
6  select
7     FLOOR(num/60)||' hrs  '||MOD(num,60)||' minutes' as "Hours.Mins"
8  from xx;

Hours.Mins
----------------------------------------------------------------------------

-9 hrs  -57 minutes
157 hrs  16 minutes
208 hrs  45 minutes

SQL> ``````
SY.
• ###### 6. Re: I want to convert number to hours in oracle 10 like this.
Dear Sir

please can u send me ur function mins_2_hours
• ###### 7. Re: I want to convert number to hours in oracle 10 like this.
As I said before, show what code you have tried. This sounds like a college homework question. You will learn nothing by copying my solution. Make an effort yourself, then ask if you hit problems.

Edited by: JohnWatson on Jan 1, 2013 2:04 PM
I see that you have just edited your original question. It is now totally different.
I see also that Ranit B has edited his answer, again to make it totally different.
Very confusing, specially when (like Ranit) there is no comment
• ###### 8. Re: I want to convert number to hours in oracle 10 like this.
Solomon Yakobson wrote:
And why are you using FLOOR and not TRUNC? OP didn't mention numbers are non-negative, right? So why provide solution that works only for non-negative numbers?
Agreed.
But, do you practically think that there is any possibility that there can be a -ve number... waiting to get converted to Hours and Mins.

And, assuming it is there, don't you think the op would have included it in his sample data?
Btw, why are you using TRUNC when the op didn't mention numbers can be negative too. Atleast, I can't see it in the sample data.

Why do you bother things which are not asked for? He might also have seconds and fractional seconds...

@John -
Don't you think creating a function would be too much, when it can be achieved by a simple query?
Why increase the no. of objects ?

Please let me know if you think otherwise.
Ranit B.

Edited by: ranit B on Jan 1, 2013 7:39 PM
--- And JohnWatson, FYKI - I worked out and modified my query 1 hr back. Check my comment also there(for you). After posting only I saw your hint... So don't say any wrong words w/o verifying properly.

Edited by: ranit B on Jan 1, 2013 7:42 PM
--- Even I'm seeing now that, op has changed his question