Make a time value from a given float value
Clients asked me to show a more detailed and longer text which doesn't have to be edited. So I came up with the following solution/function.
This function returns a varchar time value with a description containing how many days, hours and minutes. For example 100.25 returns :
'100:15( 4 Days, 4 Hours, 15 Minutes )'.
I hope somebody can use it or it was helpfull to make things easier.
Thanks in advance.
Paul
CREATE FUNCTION dbo.ReturnValidTimeInclDescription (@TIME DECIMAL (20,2))
RETURNS VARCHAR(50)
AS BEGIN
DECLARE @VALUE DECIMAL (20,2),
@RETURNVALUE VARCHAR(50)
SET @VALUE = @TIME
SET @RETURNVALUE =
CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) > 9 AND
CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) > 9 THEN
CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
'( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' +
CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
ELSE
CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) > 9 AND
CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) < 10 THEN
CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':0' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
'( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' +
CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
ELSE
CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) < 10 AND
CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) > 9 THEN
'0' + CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
'( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' +
CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
ELSE
CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) < 10 AND
CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) < 10 THEN
'0' + CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':0' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
'( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' +
CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
ELSE
'( Unassignend value)'
END
END
END
END
RETURN @RETURNVALUE
END 
