I want to convert number to hours in oracle 10g like this.Try this...
537 8 hours 57 minutes
9436 157 hours 16 minutes
12525 208 hours 45 minutes
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 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() 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. Solomon Yakobson wrote:Agreed.
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?