How to Convert Hh:Mm:Ss to Seconds in SQL Server with More Than 24 Hours

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

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

Convert HHH:MM:SS to seconds

Just another option with a small tweak to your original

Example

Declare @V varchar(50) = '832:24:12'

Select (left(@V,charindex(':',@V)-1)*3600) + (left(right(@V,5),2)*60) + right(@v,2)

Returns

2996652

Mass convert HH:MM:SS to seconds and update

[Edit] After futher examination it appears the implicit conversion doesn't work for all of the sample data provided. The code below parses the varchar times into components and multiplies.

drop table if exists strings;
go
create table strings(time_str   varchar(30));

insert strings(time_str) values
('4:01'),
('3:45'),
('30:50'),
('1:30:45')

select t_split.l2+t_split.m2*60+t_split.f2*3600 Seconds
from strings s
     cross apply 
     (select isnull(cast(substring(s.time_str, len(s.time_str)-1, 2) as int), 0) l2,
             isnull(cast(substring(s.time_str, len(s.time_str)-4, 2) as int), 0) m2,
             isnull(cast(substring(s.time_str, len(s.time_str)-7, 2) as int), 0) f2) t_split;

Update statement

update s
set Seconds=(t_split.l2+t_split.m2*60+t_split.f2*3600)
from strings s
     cross apply
      (select isnull(cast(substring(s.time_str, len(s.time_str)-1, 2) as int), 0) l2,
              isnull(cast(substring(s.time_str, len(s.time_str)-4, 2) as int), 0) m2,
              isnull(cast(substring(s.time_str, len(s.time_str)-7, 2) as int), 0) f2) t_split;

Results

Seconds
241
225
1850
5445

convert HH:MM:SS.MS to seconds using SQL Server 2008 R2

Any hour value > 24 is not really usable with the TIME type. As all but the first field are fixed length you could:

;with T(f) as (select '97:18:59.32')

select 
    (LEFT(f, CHARINDEX(':', f) - 1)  * 60 * 60)
    + (SUBSTRING(f, CHARINDEX(':', f) + 1, 2) * 60)
    + SUBSTRING(f, CHARINDEX(':', f) + 4, 2)
    + CASE WHEN (SUBSTRING(f, CHARINDEX(':', f) + 7, LEN(f)) >= 50) 
             THEN 1 ELSE 0 END
from T

Or

select 
    (LEFT(f, CHARINDEX(':', f) - 1)  * 60 * 60) 
    + CAST(ROUND(DATEDIFF(MILLISECOND, 0, '00' 
    + (SUBSTRING(f, CHARINDEX(':', f), LEN(f)))) / 1000.0, 0) AS INT)
from T

Convert HH:MM:SS

If column type is datetime then:

(DATEPART(hh, @yourValue) * 60 * 60) + (DATEPART(mi, @yourValue) * 60) + DATEPART(s, @yourValue)

Datepart reference

Return greater then 24 hours in hh:mm:ss format in SQL server 2008

BTW there are so many technical flaws in proc as well as data type that you are using.Real Problem is here.

1. In your proc you can put where date condition in first CTE itself.
2. If possible you can store extrahour in second in table.That means Extrahours is int or bigint.like -1000 or 1000.varchar will never solve your problem.It will save you from lot of conversion hence fast.
3. Using so many columns in group by is itself wrong approach.specially using varchar column in group by.You should use key column in group by then join again with table to get other columns in resultset.

with your sample data I am getting -29:-51:-30.0 instead of -31:50:46 .
do it this way,

DECLARE @t TABLE (ExtraHrs VARCHAR(20))

INSERT INTO @t
VALUES ('00:59:38')
    ,('-03:59:37')
    ,('-08:59:39')
    ,('-08:52:36')
    ,('-08:59:16');

WITH cte
AS (
    SELECT ExtraHrs
        ,CASE 
            WHEN left(ExtraHrs, 1) = '-'
                THEN - 1
            ELSE 1
            END AS multiply
        ,right(ExtraHrs, 8) AS timestring
        ,
        --get hours in seconds:
        DATEPART(HOUR, right(ExtraHrs, 8)) * 3600 AS h_in_s
        ,
        --get minutes in seconds:
        DATEPART(MINUTE, right(ExtraHrs, 8)) * 60 AS m_in_s
        ,
        --get seconds:
        DATEPART(SECOND, right(ExtraHrs, 8)) AS s
    FROM @t
    )
    ,CTE3
AS (
    SELECT *
        ,c.h_in_s + c.m_in_s + c.s AddExtra
    FROM cte c
    )
    ,cte4
AS (
    SELECT sum(AddExtra * multiply) mn
    FROM cte3
    )
    ,cte5
AS (
    SELECT mn / 3600 hh
        ,(mn % 3600) / 60 mi
        ,(mn % 3600.0) % 60 ss
    FROM cte4
    )
SELECT CASE 
        WHEN hh < 0
            THEN '-'
        ELSE ''
        END
    ,cast(hh AS VARCHAR) + ':' + cast(mi AS VARCHAR) + ':' + cast(ss AS VARCHAR)
FROM cte5

Try this for leading zero,

Declare @i int=5 SELECT REPLICATE('0',2-LEN(@i)) + cast(@i as varchar)

---

Related Topics