8 Replies Latest reply on Jan 1, 2013 2:13 PM by ranit B

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

    Parwez
      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
          What code have you tried so far?
          • 3. Re: I want to convert number to hours in oracle 10 like this.
            ranit B
            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
              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
                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
                  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
                    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
                      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