How to convert hh:mm:ss to seconds in SQL Server w

问题:

I have table name tblAttend in which one column named WorkHrs is of datatype 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 WorkHrs is greater than 24 hours it will throw an error:

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

回答1:

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

回答2:

Try this:

SELECT DATEDIFF(SECOND, CONVERT(DATE,GETDATE()), GETDATE())

回答3:

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

回答4:

You need to convert your WorkHrs to DATETIME first, then perform the 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

回答5:

 ;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

回答6:

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

回答7:

You may try like this:

SELECT Sec=SUM((DATEPART(HOUR,column name)*3600)+(DATEPART(MINUTE,column name)*60)+(DATEPART(Second,column name)))
FROM [TableName]