How to convert Seconds to HH:MM:SS using T-SQL

Question

The situation is you have a value in Seconds (XXX.XX), and you want to convert to HH:MM:SS using T-SQL.

Example:

• 121.25 s becomes 00:02:01.25

Answer 1

Using SQL Server 05 I can get this to work by using:

declare @OrigValue int;
set @OrigValue = 121.25;
select replace(str(@OrigValue/3600,len(ltrim(@OrigValue/3600))+abs(sign(@OrigValue/359999)-1)) + ':' + str((@OrigValue/60)%60,2) + ':' + str(@OrigValue%60,2),' ','0')

Answer 2

Convert seconds to hours in select query:

SELECT field_in_seconds,round((field_in_seconds / 3600),2) FROM a_table;

This will give you the result in decimal format. Not sure how to convert 1.50 to 1:30. Please feel free to edit my code!

Answer 3

For those having issues with using DATEADD and CONVERT for seconds exceeding 24 hours, we can use modulus to get around that:

SELECT CONVERT(varchar, @seconds / 86400 ) + ':' + -- Days
CONVERT(varchar, DATEADD(ms, ( @seconds % 86400 ) * 1000, 0), 114)
as "Converted to D:HH:MM:SS.MS"

Answer 4

DECLARE @Seconds INT = 86200;
SELECT 
CONVERT(VARCHAR(15), 
CAST(CONVERT(VARCHAR(12), @Seconds / 60 / 60 % 24)
+':'+ CONVERT(VARCHAR(2), @Seconds / 60 % 60)
+':'+ CONVERT(VARCHAR(2), @Seconds % 60) AS TIME), 100) AS [HH:MM:SS (AM/PM)]

Answer 5

If your time amount exceeds 24 hours it won't be handled correctly with the DATEADD and CONVERT methods.

SELECT CONVERT(varchar, DATEADD(ms, 24*60*60 * 1000, 0), 114)
00:00:00:000

The following function will handle times exceeding 24 hours (~max 35,791,394 hours).

create function [dbo].[ConvertTimeToHHMMSS]
(
    @time decimal(28,3), 
    @unit varchar(20)
)
returns varchar(20)
as
begin

    declare @seconds decimal(18,3), @minutes int, @hours int;

    if(@unit = 'hour' or @unit = 'hh' )
        set @seconds = @time * 60 * 60;
    else if(@unit = 'minute' or @unit = 'mi' or @unit = 'n')
        set @seconds = @time * 60;
    else if(@unit = 'second' or @unit = 'ss' or @unit = 's')
        set @seconds = @time;
    else set @seconds = 0; -- unknown time units

    set @hours = convert(int, @seconds /60 / 60);
    set @minutes = convert(int, (@seconds / 60) - (@hours * 60 ));
    set @seconds = @seconds % 60;

    return 
        convert(varchar(9), convert(int, @hours)) + ':' +
        right('00' + convert(varchar(2), convert(int, @minutes)), 2) + ':' +
        right('00' + convert(varchar(6), @seconds), 6)

end

Usage:

select dbo.ConvertTimeToHHMMSS(123, 's')
select dbo.ConvertTimeToHHMMSS(96.999, 'mi')
select dbo.ConvertTimeToHHMMSS(35791394.999, 'hh')
0:02:03.000
1:36:59.940
35791394:59:56.400

Answer 6

SELECT substring(convert (varchar(23),Dateadd(s,10000,LEFT(getdate(),11)),121),12,8)

10000 is your value in sec