如何在超过 24 小时的 SQL Server 中将 hh:mm:ss 转换为秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31425204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert hh:mm:ss to seconds in SQL Server with more than 24 hours
提问by Waqas
I have table name tblAttendin which one column named WorkHrsis of datatype varchar.
我有表名tblAttend，其中一列名为WorkHrsdatatype varchar。
The result of simple select query is
简单选择查询的结果是
I sum this column's value and get result in seconds my query is
我总结了这个列的值并在几秒钟内得到结果我的查询是
select sum(DATEDIFF(SECOND, '0:00:00', WorkHrs ))
from tblAttend
and it shows this output:
它显示了这个输出：
Now the issue is, when sum of WorkHrsis greater than 24 hours it will throw an error:
现在的问题是，当总和WorkHrs大于 24 小时时，它会抛出错误：
What can you suggest to get around this problem? Thanks in advance
你有什么建议可以解决这个问题？提前致谢
回答by Ewan
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
回答by Lord Dorn
Try this:
尝试这个：
SELECT DATEDIFF(SECOND, CONVERT(DATE,GETDATE()), GETDATE())
回答by merrais
I have implemented the following function to use it in the management of my projects :
我已经实现了以下功能以在我的项目管理中使用它：
/****** Object: UserDefinedFunction [dbo].[Seconds] Script Date: 10/6/2017 12:00:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
select [dbo].[Seconds]('24:00:00'),(24*3600)
select [dbo].[Seconds]('102:56:08'),(102*3600+56*60+8)
*/
ALTER FUNCTION [dbo].[Seconds] (@Time as varchar(50))
RETURNS int
BEGIN
declare @S int, @H int
set @H=cast(SUBSTRING(@Time,1,CHARINDEX(':',@Time)-1) as int)
IF @H<24
set @S=DATEDIFF(SECOND, '0:00:00', @Time)
ELSE BEGIN
set @H=@H-23
set @Time = '23'+SUBSTRING(@Time,CHARINDEX(':',@Time),LEN(@Time)-2)
set @S = (@H*3600)+DATEDIFF(SECOND, '0:00:00', @Time)
END
RETURN @S
END
回答by Junior Gr?o
I think you can isolate each part of the time (hour, minute and second) and than sum what you need, please take a look:
我认为您可以隔离时间的每个部分（小时，分钟和秒），然后总结您需要的内容，请看一看：
declare @tbl table(WorkHrs VARCHAR(8))
insert into @tbl(WorkHrs) values ('02:29:11')
insert into @tbl(WorkHrs) values ('25:00:11')
-- Sum in minutes
SELECT TRY_CAST(([HOURS] * 60) + [MINUTES] + ([SECOND] / 60) AS INT) as TotalInMinutes
FROM (
SELECT
-- Use this aproach to get separated values
SUBSTRING(WorkHrs,1,CHARINDEX(':',WorkHrs)-1) AS [HOURS],
SUBSTRING(WorkHrs,4,CHARINDEX(':',WorkHrs)-1) AS [MINUTES],
SUBSTRING(WorkHrs,7,CHARINDEX(':',WorkHrs)-1) AS [SECOND] -- probably you can ignore this one
FROM @tbl
)
tbl
-- Or try to sum in seconds
SELECT TRY_CAST(([HOURS] * 3600) + ([MINUTES] * 60) + [SECOND] AS INT) as TotalInSeconds
FROM (
SELECT
-- Use this aproach to get separated values
SUBSTRING(WorkHrs,1,CHARINDEX(':',WorkHrs)-1) AS [HOURS],
SUBSTRING(WorkHrs,4,CHARINDEX(':',WorkHrs)-1) AS [MINUTES],
SUBSTRING(WorkHrs,7,CHARINDEX(':',WorkHrs)-1) AS [SECOND]
FROM @tbl
)
tbl
It will return like this to you:
它会像这样返回给你：
I hope it can help
我希望它可以帮助
回答by Subash
You may try like this:
你可以这样尝试：
SELECT Sec=SUM((DATEPART(HOUR,column name)*3600)+(DATEPART(MINUTE,column name)*60)+(DATEPART(Second,column name)))
FROM [TableName]
回答by Felix Pamittan
You need to convert your WorkHrsto DATETIMEfirst, then perform the DATEDIFF:
您需要先将您的转换WorkHrs为DATETIME，然后执行DATEDIFF：
WITH Cte(WorkHrs) AS(
SELECT CAST('02:29:11' AS VARCHAR(10)) UNION ALL
SELECT CAST('21:00:00' AS VARCHAR(10)) UNION ALL
SELECT CAST('25:20:02' AS VARCHAR(10))
),
CteConvert(dt) AS(
SELECT
DATEADD(
SECOND,
CAST(SUBSTRING(WorkHrs, 7, 2) AS INT),
DATEADD(
MINUTE,
CAST(SUBSTRING(WorkHrs, 4, 2) AS INT),
DATEADD(
HOUR,
CAST(SUBSTRING(WorkHrs,1, 2) AS INT),
0
)
)
)
FROM Cte
)
SELECT
SUM(DATEDIFF(SECOND, 0, dt)),
-- Formatted to hh:mm:sss
RIGHT('0' + RTRIM(CONVERT(CHAR(2), SUM(DATEDIFF(SECOND, 0, dt)) / (60 * 60))), 2) + ':' +
RIGHT('0' + RTRIM(CONVERT(CHAR(2), (SUM(DATEDIFF(SECOND, 0, dt)) / 60) % 60)), 2) + ':' +
RIGHT('0' + RTRIM(CONVERT(CHAR(2), SUM(DATEDIFF(SECOND, 0, dt)) % 60)),2)
FROM CteConvert
回答by Shane_Yo
;with cte as (
select
total =Sum(Left(WorkHrs,2) * 3600 + substring(WorkHrs, 4,2) * 60 + substring(WorkHrs, 7,2))
from tblAttend
)
select
total [Total Time in Seconds],
(total / 3600) [Total Time Hour Part],
((total % 3600) / 60) [Total Time Minute Part],
(total % 60) [Total Time Second Part]
from cte
