'Convert seconds to HH:MM:SS with hours more than 24

I want to convert 97200 seconds to HH:MM:SS format. I have used the below SQL Server syntax but it works as long as hours are less than 24 hours

select convert(varchar,dateadd(s,round(convert(double precision,round(97200,2)),2),0),114)

it gives 03:00:00:000

but I would like to see 27:00:00

sql-serversql-server-2005sql-server-2012


Solution 1:[1]

In SQL Server, try below:

WITH tmp_table AS (
    SELECT '2022-02-01 01:00:00' AS StartTime, '2022-02-01 01:31:31' AS EndTime
    UNION ALL 
    SELECT '2022-02-01 02:00:00' AS StartTime, '2022-02-01 04:00:00' AS EndTime
    UNION ALL
    SELECT '2022-02-01 02:00:00' AS StartTime, '2022-02-02 16:00:00' AS EndTime
    UNION ALL 
    SELECT '2022-02-01 02:00:00' AS StartTime, '2022-02-02 01:59:59' AS EndTime
) 

SELECT 
    StartTime,
    EndTime,
    CONCAT(REPLACE(STR(CAST(DATEDIFF(SECOND,StartTime,EndTime) / 3600 AS VARCHAR(2)), 2), SPACE(1), '0') ,RIGHT(CAST(CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND,StartTime,EndTime) * 1000, 0), 108) AS VARCHAR(8)),6)) AS "HH:MM:SS Format"
FROM tmp_table;

Result:

StartTime               EndTime               HH:MM:SS Format 
2022-02-01  01:00:00    2022-02-01 01:31:31   00:31:31
2022-02-01  02:00:00    2022-02-01 04:00:00   02:00:00
2022-02-01  02:00:00    2022-02-02 16:00:00   38:00:00
2022-02-01  02:00:00    2022-02-02 01:59:59   23:59:59

Step 1: Get the number of hours and return as a String. We are also padding this with '0' up to 2 spaces which will return the format as (HH)

REPLACE(STR(CAST(DATEDIFF(SECOND,StartTime,EndTime) / 3600 AS VARCHAR(2)), 2), SPACE(1), '0')

Step 2: The below portion of the code is returning (:MM:SS) by removing the Hour (which can reset back to 0 if the duration is longer than 24 hours). This is returned as a String

RIGHT(CAST(CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND,StartTime,EndTime) * 1000, 0), 108) AS VARCHAR(8)),6)

Step 3: Concatenate the 2 strings together to get HH:MM:SS

CONCAT(REPLACE(STR(CAST(DATEDIFF(SECOND,StartTime,EndTime) / 3600 AS VARCHAR(2)), 2), SPACE(1), '0') ,RIGHT(CAST(CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND,StartTime,EndTime) * 1000, 0), 108) AS VARCHAR(8)),6))

Solution 2:[2]

can you try this query?

DECLARE @RefDate DATETIME = CONVERT(datetime, '01.01.1900',104);
DECLARE @Seconds int = 97200;
DECLARE @CompareDate datetime = DATEADD(second, 97200, @RefDate);

SELECT @Seconds,
       CAST(DATEDIFF(HOUR, @RefDate, @CompareDate) AS varchar(100))
       + ':'
       + CAST(DATEPART(MINUTE, @CompareDate) AS varchar(2))
       + ':'
       + CAST(DATEPART(SECOND, @CompareDate) AS varchar(2))

Solution 3:[3]

…calculate the hours,minutes,secs from the total seconds and concatenate them in hh:mm:ss

declare @sec int=97200; --123426
--in case hours >=100
select concat(right(concat(0, @sec/3600), isnull(nullif(len(@sec/3600),1), 2)), ':', right(concat(0, @sec%3600/60),2), ':', right(concat(0, @sec%60),2))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chris Guzman
Solution 2 gurkan
Solution 3

Related Questions