How to convert number of minutes to hh:mm format in TSQL?

Question

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format.

Duration has values like 60, 120,150

For example:

60 becomes 01:00 hours

120 becomes 02:00 hours

150 becomes 02:30 hours

Also, this is how I retrieve DURATION (Minutes)

DATEDIFF(minute, FirstDate,LastDate) as 'Duration (Minutes)'

Solution 1

You can convert the duration to a date and then format it:

DECLARE
    @FirstDate datetime,
    @LastDate datetime

SELECT
    @FirstDate = '2000-01-01 09:00:00',
    @LastDate = '2000-01-01 11:30:00'

SELECT CONVERT(varchar(12), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30:00:000 */

For less precision, modify the size of the varchar:

SELECT CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30 */

Solution 2

Thanks to A Ghazal, just what I needed. Here's a slightly cleaned up version of his(her) answer:

create FUNCTION [dbo].[fnMinutesToDuration]
(
    @minutes int 
)
RETURNS nvarchar(30)

-- Based on http://stackoverflow.com/questions/17733616/how-to-convert-number-of-minutes-to-hhmm-format-in-tsql

AS

BEGIN

return rtrim(isnull(cast(nullif((@minutes / 60)
                                , 0
                               ) as varchar
                        ) + 'h '
                    ,''
                   )
            + isnull(CAST(nullif((@minutes % 60)
                                 ,0
                                ) AS VARCHAR(2)
                         ) + 'm'
                     ,''
                    )
            )

end

Solution 3

select convert(varchar(5),dateadd(mi,DATEDIFF(minute, FirstDate,LastDate),'00:00'),114)    

Solution 4

In case someone is interested in getting results as 60 becomes 01:00 hours, 120 becomes 02:00 hours, 150 becomes 02:30 hours, this function might help:

    create FUNCTION [dbo].[MinutesToHHMM]
(
    @minutes int 
)
RETURNS varchar(30)
AS

BEGIN
declare @h int
        set @h= @minutes / 60
        declare @mins varchar(2)
        set @mins= iif(@minutes%60<10,concat('0',cast((@minutes % 60) as varchar(2))),cast((@minutes % 60) as varchar(2)))
return iif(@h <10, concat('0', cast(@h as varchar(5)),':',@mins)
        ,concat(cast(@h as varchar(5)),':',@mins))

end

View more solutions

Comments

[Learner]

Awesome, less precision option worked perfectly well. First and Last dates are already date-time formatted. So I just needed last 2 lines.

[Ads]

This is great and was helpful for solving my issue too, but just a word of warning, it doesn't handle negative differences. SELECT CONVERT(varchar(5), DATEADD(minute, -90, 0), 114) I have -90 and it gives me 22:30, rather than "-1:30"

[Mike S.]

I also had the issue with negative times. The solution here resolves it: Answer titled "Lean and simple conversion" CASE WHEN RPAYCODE.TIMEINSECONDS < 0 THEN '-' ELSE '' END -- sign + CONVERT(varchar, DATEADD(s, ABS(RPAYCODE.TIMEINSECONDS), 0), 108) -- hh:mm:ss @Ads

[David Rogers]

Careful, If you want accurate high precision(like 12) you need to change the "DateDiff" to "millisecond"(or the appropriate diff), as the inner function returns a int.

[MikeTeeVee]

This does not handle counting beyond 23:59. If you had a calculation that produced something like 102:00 Hours, then this would erroneously contextualize 06:00 Hours.