本文介绍了如何总结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中的时间字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

11-03 12:08