How to convert hh:mm:ss to seconds in SQL Server with more than 24 hours

Question

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

Answer 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

Answer 2

Try this:

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

Answer 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