Wrote this bad boy last year for a job which returned the time between two events as a integer representing minutes. It needed to be output in an idiot-proof fashion e.g. 1 day, 6 hours, 12 minutes instead of just 1812 minutes.
It only really needed to cover a period of up to a couple of days but I expanded it to be able to handle any number that can be handled by an integer data type going up to n years - call it future-proofing if you like but its just because you never do know . . .
CREATE FUNCTION fn_MinutesToWords (@Minutes INT)
RETURNS varchar(100)
AS
/*
Author: Nick Holt 2010
*/
BEGIN
--DECLARE @Minutes INT
--SET @Minutes = 1501
--SELECT dbo.fn_MinutesToWords (1812)
DECLARE @Words VARCHAR(100)
SET @Words = ''
DECLARE @Hours INT
DECLARE @Days INT
DECLARE @Weeks INT
DECLARE @Years INT
IF @Minutes < 60
BEGIN
SET @Words = CASE WHEN @Minutes = 1 THEN CAST(@Minutes AS VARCHAR) + ' minute' ELSE CAST(@Minutes AS VARCHAR) + ' minutes' END
END
IF @Minutes BETWEEN 60 AND 1439
BEGIN
SET @Hours = @Minutes / 60
SET @Minutes = @Minutes % 60
SET @Words = CASE WHEN @Hours = 1 THEN CAST(@Hours AS VARCHAR) + ' hour, ' ELSE CAST(@Hours AS VARCHAR) + ' hours, ' END +
CASE WHEN @Minutes = 1 THEN CAST(@Minutes AS VARCHAR) + ' minute' ELSE CAST(@Minutes AS VARCHAR) + ' minutes' END
END
IF @Minutes BETWEEN 1440 AND 10079
BEGIN
SET @Days = @Minutes / 1440
SET @Minutes = @Minutes % 1440
SET @Hours = @Minutes / 60
SET @Minutes = @Minutes % 60
SET @Words = CASE WHEN @Days = 1 THEN CAST(@Days AS VARCHAR) + ' day, ' ELSE CAST(@Days AS VARCHAR) + ' days, ' END +
CASE WHEN @Hours = 1 THEN CAST(@Hours AS VARCHAR) + ' hour, ' ELSE CAST(@Hours AS VARCHAR) + ' hours, ' END +
CASE WHEN @Minutes = 1 THEN CAST(@Minutes AS VARCHAR) + ' minute' ELSE CAST(@Minutes AS VARCHAR) + ' minutes' END
END
IF @Minutes BETWEEN 10080 AND 524159
BEGIN
SET @Weeks = @Minutes / 10080
SET @Minutes = @Minutes % 10080
SET @Days = @Minutes / 1440
SET @Minutes = @Minutes % 1440
SET @Hours = @Minutes / 60
SET @Minutes = @Minutes % 60
SET @Words = CASE WHEN @Weeks = 1 THEN CAST(@Weeks AS VARCHAR) + ' week, ' ELSE CAST(@Weeks AS VARCHAR) + ' weeks, ' END +
CASE WHEN @Days = 1 THEN CAST(@Days AS VARCHAR) + ' day, ' ELSE CAST(@Days AS VARCHAR) + ' days, ' END +
CASE WHEN @Hours = 1 THEN CAST(@Hours AS VARCHAR) + ' hour, ' ELSE CAST(@Hours AS VARCHAR) + ' hours, ' END +
CASE WHEN @Minutes = 1 THEN CAST(@Minutes AS VARCHAR) + ' minute' ELSE CAST(@Minutes AS VARCHAR) + ' minutes' END
END
IF @Minutes >= 524160
BEGIN
SET @Years = @Minutes / 524160
SET @Minutes = @Minutes % 524160
SET @Weeks = @Minutes / 10080
SET @Minutes = @Minutes % 10080
SET @Days = @Minutes / 1440
SET @Minutes = @Minutes % 1440
SET @Hours = @Minutes / 60
SET @Minutes = @Minutes % 60
SET @Words = CASE WHEN @Years = 1 THEN CAST(@Years AS VARCHAR) + ' years, ' ELSE CAST(@Years AS VARCHAR) + ' yearss, ' END +
CASE WHEN @Weeks = 1 THEN CAST(@Weeks AS VARCHAR) + ' week, ' ELSE CAST(@Weeks AS VARCHAR) + ' weeks, ' END +
CASE WHEN @Days = 1 THEN CAST(@Days AS VARCHAR) + ' day, ' ELSE CAST(@Days AS VARCHAR) + ' days, ' END +
CASE WHEN @Hours = 1 THEN CAST(@Hours AS VARCHAR) + ' hour, ' ELSE CAST(@Hours AS VARCHAR) + ' hours, ' END +
CASE WHEN @Minutes = 1 THEN CAST(@Minutes AS VARCHAR) + ' minute' ELSE CAST(@Minutes AS VARCHAR) + ' minutes' END
END
RETURN @Words
--SELECT @Words
END
---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club
http://www.hollowtreewebdesign.co.uk - a web design company in its infancy
Function to convert minutes into words
Posted - 2011-06-29 : 09:51:19
