This content has been marked as final.
Show 5 replies

1. Re: how to sum hours which is varchar2 data type in oracle
CraigB Jan 14, 2013 6:32 PM (in response to ChakravarthyDBA)Well, first you have posted your question in the wrong forum. You should have posted your question in the PL/SQL and 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:
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: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>
Hope this helps.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>
Craig... 
2. Re: how to sum hours which is varchar2 data type in oracle
ChakravarthyDBA Jan 14, 2013 6:44 PM (in response to CraigB)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 Jan 14, 2013 6:59 PM (in response to CraigB)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 Jan 14, 2013 8:04 PM (in response to ChakravarthyDBA)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 Jan 14, 2013 8:15 PM (in response to user346369)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
;