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

    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?
          • 2. Re: I want to convert number to hours in oracle 10 like this.
            Osama_Mustafa
            More Information Please.
            • 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