Example usage:
SELECT FormatDuration(500); -- 8:20 SELECT FormatDuration(3500); -- 58:20 SELECT FormatDuration(21000); --5:50:00 SELECT FormatDuration(1234567); -- 342:56:07
--- -- Takes seconds as an integer and returns a HHH:MM:SS formatted VARCHAR. -- Note that this does not simply extract the time value, but returns the -- total time, each day adding 24 to the hour value. ---- CREATE FUNCTION FormatDuration(@seconds INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @out VARCHAR(100); DECLARE @buffer INT; SET @buffer = @seconds % 60; IF (@seconds >= 60) BEGIN SET @out = CONVERT(VARCHAR, @buffer, 1); IF (@buffer < 10) SET @out = '0' + @out; SET @buffer = FLOOR((@seconds / 60) % 60); IF (@seconds >= 3600) BEGIN SET @out = CONVERT(VARCHAR, @buffer, 1) + ':' + @out; IF (@buffer < 10) SET @out = '0' + @out; SET @out = CONVERT(VARCHAR, FLOOR(@seconds / 3600), 1) + ':' + @out; END ELSE SET @out = CONVERT(VARCHAR, @buffer, 1) + ':' + @out; END ELSE SET @out = @buffer RETURN @out END; GO