SQL Seconds to Day, Hour, Minute, Second

ahsansharjeel

Hi Experts I have a query which needs to return the second in Day, Hour, Minute, Second format. The below code works fine when its less than a day, but does not work, when the value in second is greater than a day PRINT Convert(VarChar, DateAdd(S, 86400, 0), 108) 86400 is exactly a day and it returns 00:00:00 Can someone modify it and show me the result something like this 1:00:00:00. Thanks

Mycroft Holmes

Take a look at MODULO from BOL! SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder ; Use a case statement to detect if it is over 86399 and apply the appropriate formula. If you use it in multiple places then create your own UDF.

Never underestimate the power of human stupidity RAH

cbeglobal

Try the option, CONVERT(VARCHAR(10),'fieldname',112)

Mycroft Holmes

Try replying to the op next time, he will not get notification when you reply to me!

Never underestimate the power of human stupidity RAH

Hitesh R

try this :

DECLARE @DurationSeconds INT
DECLARE @DurationDays INT
SET @DurationSeconds = 62110
SET @DurationDays = @DurationSeconds /86400

Select CASE WHEN @DurationDays > 0 THEN
Convert(VarChar,@DurationDays)+ ':'+
Convert(VarChar, DateAdd(S, (@DurationSeconds-(@DurationDays*86400)),0), 108)
ELSE
Convert(VarChar, DateAdd(S, @DurationSeconds, 0), 108)
END