Jul 28, 2016

How to convert total number of minute into hour format?

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: