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?
A:
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.
David M
2010-06-18 08:44:22
+1
A:
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)
SPE109
2010-06-18 12:11:19