如何总结SQL Server中的时间字段

问题描述

我有一个名为WrkHrs的列，数据类型为time（hh：mm：ss）。我想总结员工的工作时间。但由于时间数据类型sql server不允许我使用像 sum（columnname）。

I have a column called "WrkHrs" and the data type is time(hh:mm:ss). I want to sum up the working hours for employees. But since it's time data type sql server doesn't let me use like sum(columnname).

我总结了在sql查询中定义的时间数据类型？

How can I sum up the time data type fieled in sql query?

推荐答案

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

你可以在前端格式化。或在T-SQL中：

You can format it pretty on the front end. Or in T-SQL:

;WITH w(e, mw) AS
(
    SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
    FROM dbo.table 
    -- WHERE ...
    GROUP BY EmployeeID
)
SELECT EmployeeID = e,
  WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)
  FROM w;

但是，您使用的数据类型错误。 TIME 用于指示时间点，而不是间隔或持续时间。将它们的工作时间存储在两个不同的列中是没有意义的， StartTime 和 EndTime ？

However, you're using the wrong data type. TIME is used to indicate a point in time, not an interval or duration. Wouldn't it make sense to store their work hours in two distinct columns, StartTime and EndTime?

这篇关于如何总结SQL Server中的时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！