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

Nonessential

sqlservercentral.com/Forums/FindPost1267364.aspx – Lotty

Answer 1

You want to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 121.25 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)

Incarnate

SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114) is it. – Nonessential

This won't work if you have more than 24 hours worth of seconds – Hastate

Very neat. Use 108 if you don't want milliseconds, ie hh:mi:ss – Ainslie

If you have SQL 2008+ and just want the time portion; instead of converting to varchar: SELECT CONVERT(time, DATEADD(ms, 121.25 * 1000, 0)) – Unexpected

You can also do FORMAT(..., 'mm:ss') – Gerta

Answer 2

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

Zumwalt

Why the time longer then 24 hours will not be formatted correctly using DateAdd? – Hymnody

@gotqn: DateAdd() isn't the problem. Convert() is the problem. Convert() effectively wraps around to zero every 24 hours. If you use Convert() to format a 27-hour interval, it will return "03:00:00". – Dipeptide

How to handle this formatting if the use case says results can be negative hh:mm:ss (eg. -340:23:34, 03:-4:05,13:54:-8 etc) – Wartow

This is the way to go. None of the other solutions here will work properly for HHMMSS formatting beyond 24 hours. This will also help keep your queries neat. – Ba

Just be aware of the performance implications this function may have on larger datasets, and test accordingly. Especially with it being non-inlined causing any dependent queries (at any level) to not be applicable for parallelization. – Masaryk

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"

Moramorabito

Answer 4

DECLARE @seconds AS int = 896434;
SELECT
    CONVERT(varchar, (@seconds / 86400))                --Days
    + ':' +
    CONVERT(varchar, DATEADD(ss, @seconds, 0), 108);    --Hours, Minutes, Seconds

Outputs:

10:09:00:34

Suisse

This should be the accepted answer -- it deals with seconds, as the O.P. asked, avoids using an inefficient scalar function, and deals with multi-day spans appropriately. The only minor nit I would add is that, in SQL Agent land, your elapsed-time/time-intervals for the "days" part typically use "days.hh:mm:ss", i.e. a dot instead of colon. So for example 2.01:03:04 for 2 days, 1 hour, 3 minutes, 4 seconds. Cheers! – Frye

Answer 5

Using SQL Server 2008

declare @Seconds as int = 3600;
SELECT CONVERT(time(0), DATEADD(SECOND, @Seconds, 0)) as 'hh:mm:ss'

Ramonramona

let me know the reason for downvoting. – Ramonramona

Can't assign the result to a variable For example: – Ratcliffe

Answer 6

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')

Trustbuster

this did not work for me. if you pass 896434 seconds - you want 10:09:00:34:000 and not 249:00:34 – Canaday

Answer 7

Just in case this might be still interesting to anyone. The 'Format' Function can also be used, with SQL Server 2012+

Declare @Seconds INT = 1000000;
SELECT FORMAT(CAST(@Seconds/86400.000 AS datetime), 'HH:mm:ss');

OR

Declare @Seconds INT = 1000000;
SELECT CAST(FORMAT(CAST(@Seconds/86400.000 AS datetime), 'HH:mm:ss') AS TIME);

Stalwart

The FORMAT function is about 22 times slower than just about any other method you could come up with. – Thought

I'll take your word for it. Have never had queries where this impacted performance. – Stalwart

Answer 8

DECLARE @TimeinSecond INT
SET @TimeinSecond = 340 -- Change the seconds
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

Pentastyle

this did not work for me. if you pass 896434 seconds - you want 10:09:00:34 and not 49:00:34 – Canaday

Answer 9

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

10000 is your value in sec

Yost

Answer 10

This is what I use (typically for html table email reports)

declare @time int, @hms varchar(20)
set @time = 12345
set @hms = cast(cast((@Time)/3600 as int) as varchar(3)) 
  +':'+ right('0'+ cast(cast(((@Time)%3600)/60 as int) as varchar(2)),2) 
  +':'+ right('0'+ cast(((@Time)%3600)%60 as varchar(2)),2) +' (hh:mm:ss)'
select @hms

Allsun

Answer 11

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)]

Underbred

Answer 12

You can try this

set @duration= 112000
SELECT 
   "Time" = cast (@duration/3600 as varchar(3)) +'H'
         + Case 
       when ((@duration%3600 )/60)<10 then
                 '0'+ cast ((@duration%3600 )/60)as varchar(3))
       else 
               cast ((@duration/60) as varchar(3))
       End

Jdavie

Answer 13

I use this:

cast(datediff(hh, '1900-01-01', dateadd(s, @Seconds), 0)) as varchar(10))
+ right(convert(char(8), dateadd(s, @Seconds), 0), 108),6) AS [Duration(H:MM:SS)]

Pyszka

Answer 14

Maybe you can use this:

To get a value with time datatype:

select convert(time(0),cast(0 as datetime)+3661*1e0/24/60/60)

To get a value with char datatype:

select convert(char,cast(0 as datetime)+3661*1e0/24/60/60,108)

• time(0) returns a time with minimal precision (hh:mm:s)
• cast(0 as datetime) returns 1900-01-01 ...
• 3661 is your number in seconds= 1 hour 1 minute 1 second
• 1e0 prevent truncations (it's the number 1 expanded with decimal)
• datetime+1= tomorrow, and datetime+1e0/24/60/60= 1 second
• or replace that, with .../24/3600 (one less calculation)

Output:

01:01:01

Lawsuit

Answer 15

CAST((Duration)/60 AS NVARCHAR(9)) + ':' + RIGHT('00' + CAST(Duration % 60 AS NVARCHAR(9)), 2)

Mcgill

Please don't post code-only answers. Future readers will be grateful to see explained why this answers the question instead of having to infer it from the code. Also, since this is an old question, please explain how it complements all other answers. (BTW, the answer isn't correct) – Shoon