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

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

Richard, that solution rocks. Solves the > 24hr issue too!