This discussion is archived
5 Replies Latest reply: Jan 14, 2013 12:15 PM by ChakravarthyDBA RSS

how to sum hours which is varchar2 data type in oracle

ChakravarthyDBA Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Well, first you have posted your question in the wrong forum. You should have posted your question in the SQL and 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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    ;

Legend

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