CODEWITHSUNDEEP
sqldatabasesql-server-2008
Renuka
Renuka

Reputation: 206

Sum up Time column using sql query

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

Upvotes: 4

Views: 21341

Answers (5)

Dinkar Veer
Dinkar Veer

Reputation: 69

I know this question is to old but i found best option for this. Try this:

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

Upvotes: 0

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

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.

Upvotes: 4

Ace Amr
Ace Amr

Reputation: 108

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)

Upvotes: 3

user359040
user359040

Reputation:

Try:

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

- to get the result in seconds.

Upvotes: 0

Freelancer
Freelancer

Reputation: 9064

Try Following: 

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

Upvotes: 1

Related Questions