5 Replies Latest reply: Jan 14, 2013 2:15 PM by ChakravarthyDBA RSS

    how to sum hours which is varchar2 data type in oracle

    ChakravarthyDBA
      Hi My table is like this
      emp_ngtshthrs (empno number(10),nightshifthrs varchar2(20));

      now I want sum employee nightshifthrs how to do sum of hrs, this is my hours data 01:00,05:00,08:00,10:00,07:00 and 09:00
      I want sum the varchar2 type of hours how to do it? and I want to display even the sum is more than 24:00 hrs
        • 1. Re: how to sum hours which is varchar2 data type in oracle
          CraigB
          Well, first you have posted your question in the wrong forum. You should have posted your question in the PL/SQL forum.

          The second problem I see is that you are being too generic when you have your employees enter their night shift hours worked. If you are able, I recommend you modify your table to record hours seperately from minutes and make the columns of type NUMBER instead of type VARCHAR2(). Then you can use simply arithmatic to total the hours and minutes worked.

          If you are locked into your table and can't change it, then you can convert the characters to numbers and then perform your summary arithmatic on the values. For example:
            1  with tab1 as (
            2  select 10 as empno, '01:00' as nightshifthrs from dual union all
            3  select 10 as empno, '05:00' as nightshifthrs from dual union all
            4  select 10 as empno, '08:00' as nightshifthrs from dual union all
            5  select 10 as empno, '10:00' as nightshifthrs from dual union all
            6  select 10 as empno, '07:00' as nightshifthrs from dual union all
            7  select 10 as empno, '09:00' as nightshifthrs from dual)
            8  select sum(to_number(replace(nightshifthrs,':','.'))) AS hours_worked
            9* from tab1
          SQL> /
          
          HOURS_WORKED
          ------------
                    40
          SQL> 
          Of course, if your users can and do enter minutes, then that complicates the example I provided. You will have to convert the minutes to decimal, sum the amount, then convert the decimal back to time and add this to your hours. For example:
            1  with tab1 as (
            2  select 10 as empno, '01:15' as nightshifthrs from dual union all
            3  select 10 as empno, '05:00' as nightshifthrs from dual union all
            4  select 10 as empno, '08:30' as nightshifthrs from dual union all
            5  select 10 as empno, '10:00' as nightshifthrs from dual union all
            6  select 10 as empno, '07:45' as nightshifthrs from dual union all
            7  select 10 as empno, '09:00' as nightshifthrs from dual)
            8  select sum(to_number(substr(nightshifthrs,1,2))) + SUM(to_number(SUBSTR(nightshifthrs,4,5)))/60
            9* from tab1
          SQL> /
          
          HOURS_WORKED
          ------------
                  41.5
          SQL> 
          Hope this helps.

          Craig...
          • 2. Re: how to sum hours which is varchar2 data type in oracle
            ChakravarthyDBA
            I used the below from Ask Tom

            select SUM(to_char( to_date(HRS, 'hh24:mi'), 'hh24'))||':'||SUM(to_char( to_date(HRS, 'hh24:mi'), 'mi')) HRS from TESTHRS;

            it worked for me
            • 3. Re: how to sum hours which is varchar2 data type in oracle
              ChakravarthyDBA
              Hi My query is little bit wrong you'r one is worked for me however I need the output as 45:01 instead of 45.1
              and when I am executing the query when ever I have minutes then it is not displaying properly it is showing like this 11.36666666666666666666666666666666666667 if I add 10:00,
              01:22 these two
              • 4. Re: how to sum hours which is varchar2 data type in oracle
                user346369
                Here you go:
                <pre>WITH tab1
                as (
                select 10 as empno, '01:15' as nightshifthrs from dual union all
                select 10 as empno, '05:00' as nightshifthrs from dual union all
                select 10 as empno, '08:30' as nightshifthrs from dual union all
                select 10 as empno, '10:00' as nightshifthrs from dual union all
                select 10 as empno, '07:45' as nightshifthrs from dual union all
                select 10 as empno, '09:31' as nightshifthrs from dual
                )
                select
                SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                as rawval,
                TRUNC( SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                ,0)
                as hours,
                TO_CHAR(ROUND(60*MOD(
                SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                , 1)),'fm00')
                as minutes,
                --- Above just shows intermediate values.
                --- Below is all you need for the final result:
                TRUNC( SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                ,0)
                ||':'
                || TO_CHAR(ROUND(60*MOD(
                SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                , 1)),'fm00')
                as "FinalResult"
                from tab1
                ;</pre>

                Result:
                <pre> RAWVAL HOURS MIN FinalResult
                 --------- ---------- --- ------------
                42.0166667 42 01 42:01</pre>
                • 5. Re: how to sum hours which is varchar2 data type in oracle
                  ChakravarthyDBA
                  Thank you it exactly worked for me.

                  TRUNC( SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                  + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                  ,0)
                  ||':'
                  || TO_CHAR(ROUND(60*MOD(
                  SUM(TO_NUMBER(SUBSTR(nightshifthrs,1,2)))
                  + SUM(TO_NUMBER(SUBSTR(nightshifthrs,4,5)))/60
                  , 1)),'fm00')
                  as "FinalResult"
                  from tab1
                  ;