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

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)

Answer 2

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)

Answer 3

Using SQL Server 2008

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

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

Answer 5

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