Sum of column time 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 the TotalHours ' column is obtained as diff ClockIn and ClockOut

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

How to add this column .....

I tried both

 select SUM(TotalHours) 

But it returns an error:

The varchar operand type is not valid for the sum operator

I also tried casting it to float , datetime and time ...

But everything returns an error ...

Help summarize the time column ....

Answer 1

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

 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') ; 

Request 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 | 

Request 2 :

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

Results :

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

Request 3 :

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

Results :

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

Here, the last request was taken from FreeLancers answer, as I really wanted to know if it works or not.

Here, you first need to convert the time and time difference into a second or a minute, and then convert that time back to an hour.

Hope this helps.

Answer 2

Since TotalHours is a 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 access to 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) 

Answer 3

Try the following:

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

Answer 4

Try:

 select sum(datediff(s,ClockIn,ClockOut)) 

- get the result in seconds.

Answer 5

I know this question is old, but I found a better option for this. Try the following:

 select cast(sum(datediff(second,0,dt))/3600 as varchar(12)) + ':' + right('0' + cast(sum(datediff(second,0,dt))/60%60 as varchar(2)),2) + ':' + right('0' + cast(sum(datediff(second,0,dt))%60 as varchar(2)),2) from TestTable