Convert numbers to hours and minutes in SQL

Introduction

In this tutorial we will learn how to convert minutes to HH:MM format in SQL Server using SQL Function. This SQL Function can be used throughout the database queries.

Let’s begin with querying the SQL Function

SQL Function

CREATE FUNCTION fn_minutesToHhMmFormat
(
        @minutes int
)
RETURNS nvarchar(MAX)
AS
BEGIN
  DECLARE @hours nvarchar(MAX)
    SET @hours = CASE WHEN @minutes >= 60 THEN
     (SELECT
            CASE WHEN LEN(@minutes/60) > 1
              THEN CAST((@minutes/60) AS VARCHAR(MAX))
              ELSE '0' + CAST((@minutes/60)AS VARCHAR(MAX))
            END
            +':'+
            CASE WHEN (@minutes%60) > 0
             THEN CASE WHEN LEN(@minutes%60) > 1
               THEN CAST((@minutes%60) AS VARCHAR(MAX))
               ELSE '0' + CAST((@minutes%60) AS VARCHAR(MAX))
            END
            ELSE '00'
      END)
     ELSE
      '00:' + CAST((@minutes%60) AS VARCHAR(2))
     END
  RETURN @hours
END

Once the above query is executed, you can see it in

How to use

Simply prefix “dbo.fn_minutestohhmmformat” before the database table column name or any value.

Syntax

SELECT dbo.fn_minutestohhmmformat(23)
OUTPUT: 00:23

SELECT dbo.fn_minutestohhmmformat(241)
OUTPUT: 04:01

SELECT dbo.fn_minutestohhmmformat(120)
OUTPUT: 02:00

SELECT dbo.fn_minutestohhmmformat(123456)
OUTPUT: 2057:36 

SELECT dbo.fn_minutestohhmmformat(columnName) FROM tbl_tableName