Sql Function to convert minutes to HH:MM Format

Jun 22, 2016 By Mohammed Imtiyaz

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

Demo

How to use

Simply prefix dbo.fn_minutestohhmmformat before the database table column name aor 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



Post your comment