Function to convert minutes into words

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Function to convert minutes into words

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-29 : 09:51:19

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 10:14:19

Couldn't you just
select @Hours = @Minutes / 60, @Minutes = @Minutes % 60
select @Days = @Hours / 24, @Hours = @Hours % 24
select @Weeks = @Days / 7, @Days = @Days % 7
Then deal with the words and display rather than repeating all the code?
The above could also be a single statement but I would probably sepate it like this.

Actually this could be similar to your last if statement - just cater for the conditions in the case statement rather than use if's.

Think that would make this two statements potentially - one if you used a cte.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources