如何在超过 24 小时的 SQL Server 中将 hh:mm:ss 转换为秒
问题描述：
我有一个表名 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