如何在超过 24 小时的 SQL Server 中将 hh:mm:ss 转换为秒 [英] How to convert hh:mm:ss to seconds in SQL Server with more than 24 hours

问题描述

我有一个表名 tblAttend，其中一列名为 WorkHrs 的数据类型为 varchar.

I have table name tblAttend in which one column named WorkHrs is of datatype varchar.

简单选择查询的结果是

我对这个列的值求和并在几秒钟内得到结果我的查询是

I sum this column's value and get result in seconds my query is

select sum(DATEDIFF(SECOND, '0:00:00', WorkHrs )) 
from tblAttend

它显示了这个输出:

现在的问题是，当 WorkHrs 的总和大于 24 小时时，它会抛出错误:

Now the issue is, when sum of WorkHrs is greater than 24 hours it will throw an error:

您有什么建议可以解决这个问题?提前致谢

What can you suggest to get around this problem? Thanks in advance

推荐答案

通过将时间转换为字符串，然后乘以与每个部分相关的秒数，尝试将每个时间拆分为其组成部分.

Try splitting each time into its component parts by converting the time to a string and then multiplying by the number of seconds relevant to each part.

数据到整数的转换是隐式的

Data conversion to integer is implicit

select Sum(Left(WorkHrs,2) * 3600 + substring(WorkHrs, 4,2) * 60 + substring(WorkHrs, 7,2))
from tblAttend

这篇关于如何在超过 24 小时的 SQL Server 中将 hh:mm:ss 转换为秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！