使用sql查询来求和时间列 [英] Sum up Time column using sql query

问题描述

我有一个表如下

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

这里' TotalHours 和ClockOut

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

现在我需要添加列中的所有数据 TotalHours $ c> varchar ）。

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

如何添加此列.....

How should I add this column.....

我尝试过

select SUM(TotalHours)

但它返回一个错误：

操作数数据类型varchar无效sum运算符

我也尝试将它转换为 float ， datetime 和时间 ...

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

但所有返回错误。 。

请帮助总结时间列....

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

推荐答案

SQL小提示

MS SQL Server 2008架构设置：

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

查询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 |

查询2 ：

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

结果 ：

Results:

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

查询3 ：

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

结果 ：

Results:

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

这里最后一个查询是从FreeLancers回答，因为我渴望知道它是否工作。

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.

希望这有助于。

这篇关于使用sql查询来求和时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！