This discussion is archived
8 Replies Latest reply: Jan 1, 2013 6:06 AM by JohnWatson RSS

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

Parwez Newbie
Currently Being Moderated
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.
    JohnWatson Guru
    Currently Being Moderated
    What code have you tried so far?
  • 2. Re: I want to convert number to hours in oracle 10 like this.
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    More Information Please.
  • 3. Re: I want to convert number to hours in oracle 10 like this.
    ranit B Expert
    Currently Being Moderated
    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.
    JohnWatson Guru
    Currently Being Moderated
    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.
    Solomon Yakobson Guru
    Currently Being Moderated
    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.
    Parwez Newbie
    Currently Being Moderated
    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.
    JohnWatson Guru
    Currently Being Moderated
    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.
    ranit B Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points