Is there any function in SQL Server to convert a number into Time format?

Question

I am getting a whole number for the duration of product. For example : 62, 103, 184, 4543. I want to convert it in time format. Let say , in case of 62, it would format like '00:01:02'. I am looking for a function which can resolve my problem. Is there any function in SQL Server which convert a number into time format?

Answer 1

Read How to Convert Seconds to HH:MM:SS

Answer 2

Well, you have two choices. You can do the division by 60 yourself like so:

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

Or you can do some sort of datetime-related kludge:

SELECT  CONVERT(varchar,
                DATEADD(second, @seconds, CAST('1-Jan-2000' AS datetime)),
                8)

Format 8 is hh:mi:ss from here.

Answer 3

declare @d datetime
Set @d = Convert(datetime, '20010101',112 )

Set @d = dateadd(s, 62, @d)  --add 62 seconds to base date

Select convert(varchar,@D,108)