Recently I faced a problem to convert total number of minutes into hour format. So I come through the below solution and just wanna to share with all.
-- SELECT dbo.fnGetHour(120)
CREATE FUNCTION [dbo].[fnGetHour](@minute BIGINT)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @Num2 INT = 60
DECLARE @hour nvarchar(10)
SELECT @hour =
CASE WHEN LEN(CONVERT(VARCHAR, @minute / @Num2)) = 1 THEN '0' + CONVERT(VARCHAR, @minute / @Num2) + ':' +
CASE WHEN LEN(CONVERT(VARCHAR, @minute % @Num2)) = 1 THEN '0' + CONVERT(VARCHAR, @minute % @Num2)
ELSE CONVERT(VARCHAR, @minute % @Num2)
END
ELSE CONVERT(VARCHAR, @minute / @Num2) + ':' +
CASE WHEN LEN(CONVERT(VARCHAR, @minute % @Num2)) = 1 THEN '0'+CONVERT(VARCHAR, @minute % @Num2)
ELSE CONVERT(VARCHAR, @minute % @Num2)
END
END
RETURN @hour
END
No comments:
Post a Comment