Sum up Time column using sql query

Question

I have a table as follows

repID   ClockIn     ClockOut    TotalHours
109145  7:50:50 AM  3:37:16 PM  7:46:26
109145  7:52:41 AM  3:44:51 PM  7:52:10
109145  8:42:40 AM  3:46:29 PM  7:3:49
109145  7:50:52 AM  3:42:59 PM  7:52:7
109145  8:09:23 AM  3:36:55 PM  7:27:32

Here 'TotalHours' column is obtained as diff of ClockIn and ClockOut

Now I need to add all the data in column 'TotalHours' Whose datatype in (varchar).

How should I add this column.....

I tried as

select SUM(TotalHours)

But it returns an error:

Operand data type varchar is invalid for sum operator

I also tried with casting it into float, datetime and time...

But all returns error...

Please help to sum up the time column....

Answer 1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table2
    ([repID] int, [ClockIn] datetime, [ClockOut] datetime, [TotalHours] varchar(7))
;

INSERT INTO Table2
    ([repID], [ClockIn], [ClockOut], [TotalHours])
VALUES
    (109145, '7:50:50 AM', '3:37:16 PM', '7:46:26'),
    (109145, '7:52:41 AM', '3:44:51 PM', '7:52:10'),
    (109145, '8:42:40 AM', '3:46:29 PM', '7:3:49'),
    (109145, '7:50:52 AM', '3:42:59 PM', '7:52:7'),
    (109145, '8:09:23 AM', '3:36:55 PM', '7:27:32')
;

Query 1:

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, ClockIn, ClockOut)), 0),  108)
from Table2
group by repID

Results:

| COLUMN_0 |
------------
| 14:02:04 |

Query 2:

select sum(datediff(second,ClockIn,ClockOut))/3600 as hours_worked
from Table2

Results:

| hours_worked|
------------
|       38 |

Query 3:

select sum(datediff(minute, 0, TotalHours)) / 60.0 as hours_worked
from Table2

Results:

| HOURS_WORKED |
----------------
|           38 |

Here the last query has been taken from FreeLancers answer as i was eager to know whether it works or not.

Here, First you need to convert your datetime difference into either second or minute and then convert that time back to the hour.

Hope this helps.

Answer 2

Since TotalHours is varchar , we need to convert it to time first . Try the following code

select sum(datediff(minute,'0:00:00',CONVERT(time,TotalHours)))/60.0 as TotalHoursWorked

One way to get the output in HH:MM is -

select left(right(convert(varchar(20),cast(sum(datediff(minute,'0:00:00',CONVERT(datetime,TotalHours)))/86400.0 as datetime)),8),6)