0 Replies - 1493 Views - Last Post: 05 October 2010 - 06:08 PM

#1 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

MSSQL - Convert seconds to human-readable time.

Posted 05 October 2010 - 06:08 PM

Originally written to convert music durations, stored as integer seconds, into a human-readable format.

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


Is This A Good Question/Topic? 0
  • +

Page 1 of 1