Convert Seconds into DD:HH:MM:SS - SQL Server

declare @seconds integer
set @seconds = 1000
SELECT CONVERT(varchar, DATEADD(ms, 1000 * 1000, 0), 114)

results --> 00:16:40:000

I have 1877909 seconds. How can i convert this?

DECLARE @seconds int
SET @seconds  = 135655

SELECT RIGHT ('0' + CONVERT(varchar(6), @seconds/3600),2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (@seconds % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), @seconds % 60), 2)

DECLARE @seconds int
SET @seconds  = 1877909

SELECT RIGHT ('0' + CONVERT(varchar(6), @seconds/3600),2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (@seconds % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), @seconds % 60), 2)


RESULT:

21:38:29

My approach above would result with:21:17:38:29

or try this:

DECLARE  @Seconds INT = 1000
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

select right('0' + cast (2 as varchar),2)

to convert an integer to XX format and use + between all fields to get hh:mm:ss

DECLARE  @Seconds INT = 60000
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60) 

select right('0' + cast (hh as varchar),2) + ':' + 
right('0' + cast (mm as varchar),2) + ':' + 
right('0' + cast (ss as varchar),2) as TS 
from (
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60) 
) x

Select allOpen in new window

This works for me.

I guess I'm the only one noticing that the result needs to show:Days:HH:MM:SS:)

If your column is called myseconds:

select replace(right(convert(varchar(19), dateadd(ss,  myseconds, cast('20001231' as datetime)),  20), 11), ' ', ':')

Not only you, MP. But I was too late ...

You are right matthewspatrick.

Qlemo,

Yes, but once the number of seconds >= 32 * 86400, your approach will yield the wrong answer :)

Patrick

DD is two digits, so it is unlikely to extend to more than 31 days (I hope). But you are right, that is a flaw.