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)'

Citrin

Will there ever be a situation where the duration exceeds a 24 hour period? – Sward

i got the awesome answer for this here – Ethiopic

Answer 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 */

Say

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

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" – Bolero

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 @Bolero – Wotan

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. – Acyl

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. – Shaylashaylah

Answer 2

This function is to convert duration in minutes to readable hours and minutes format. i.e 2h30m. It eliminates the hours if the duration is less than one hour, and shows only the hours if the duration in hours with no extra minutes.

CREATE FUNCTION [dbo].[MinutesToDuration]
(
    @minutes int 
)
RETURNS nvarchar(30)

AS
BEGIN
declare @hours  nvarchar(20)

SET @hours = 
    CASE WHEN @minutes >= 60 THEN
        (SELECT CAST((@minutes / 60) AS VARCHAR(2)) + 'h' +  
                CASE WHEN (@minutes % 60) > 0 THEN
                    CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
                ELSE
                    ''
                END)
    ELSE 
        CAST((@minutes % 60) AS VARCHAR(2)) + 'm'
    END

return @hours
END

To use this function :

SELECT dbo.MinutesToDuration(23)

Results: 23m

SELECT dbo.MinutesToDuration(120)

Results: 2h

SELECT dbo.MinutesToDuration(147)

Results: 2h27m

Hope this helps!

Judijudicable

how do you do days? Included – Happ

Answer 3

I'm not sure these are the best options but they'll definitely get the job done:

declare @durations table
(
    Duration int
)

Insert into @durations(Duration)
values(60),(80),(90),(150),(180),(1000)

--Option 1 - Manually concatenate the values together
select right('0' + convert(varchar,Duration / 60),2) + ':' + right('0' + convert(varchar,Duration % 60),2)
from @Durations

--Option 2 - Make use of the time variable available since SQL Server 2008
select left(convert(time,DATEADD(minute,Duration,0)),5)
from @durations

GO

Shellfish

Answer 4

For those who need convert minutes to time with more than 24h format:

DECLARE @minutes int = 7830
SELECT CAST(@minutes / 60 AS VARCHAR(8)) + ':' + FORMAT(@minutes % 60, 'D2') AS [Time]

Result:

130:30

Foskett

Answer 5

DECLARE @Duration int

SET @Duration= 12540 /* for example big hour amount in minutes -> 209h */

SELECT CAST( CAST((@Duration) AS int) / 60 AS varchar) + ':'  + right('0' + CAST(CAST((@Duration) AS int) % 60 AS varchar(2)),2)

/* you will get hours and minutes divided by : */

Selfsustaining

Leading zero for the hour part: right('0' + CAST( CAST((@Duration) AS int) / 60 AS varchar),2) + ':' + right('0' + CAST(CAST((@Duration) AS int) % 60 AS varchar(2)),2) – Shelving

Handle negative values as well: Select Case When @Duration<0 And @Duration>-60 Then '-' else '' End + CAST(@Duration/60 as varchar) + ':' + right('0' + CAST(Abs(@Duration) % 60 as varchar(2)),2) – Shipyard

If your @Duration uses Precision, then you should Cast as Decimal(10,0) BEFORE casting as Int. Doing so will round your numbers up and down. Otherwise values like 58.9 will Truncate to 0:58 (instead of 0:59) and 59.9 will Truncate to 0:59 (instead of 1:00). It all depends on your needs. I work with a 3rd Party application that doesn't handle math very well, so I'm often left with results like 4.9999999500000 that I would prefer to round. – Shaylashaylah

Answer 6

This seems to work for me:

SELECT FORMAT(@mins / 60 * 100 + @mins % 60, '#:0#')

Morphosis

Answer 7

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 https://mcmap.net/q/366385/-how-to-convert-number-of-minutes-to-hh-mm-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

Rhondarhondda

Answer 8

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

Amulet

Answer 9

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

Roup

Answer 10

I would do the following (copy-paste the whole stuff below into immediate window / query window and execute)

DECLARE @foo int
DECLARE @unclefoo smalldatetime
SET @foo = DATEDIFF(minute, CAST('2013.01.01 00:00:00' AS datetime),CAST('2013.01.01 00:03:59' AS datetime)) -- AS 'Duration (Minutes)'

SET @unclefoo = DATEADD(minute, @foo, '2000.01.01')

SELECT CAST(@unclefoo AS time)

@foo stores the value you generate in your question. The "trick" comes by then:
we create a smalldatetime variable (in my case it's yyyy.mm.dd format) and increment it with your int value, then display (or store if you want) the time part only.

Featherston

Answer 11

declare function dbo.minutes2hours (
    @minutes int
)
RETURNS varchar(10)
as
begin
    return format(dateadd(minute,@minutes,'00:00:00'), N'HH\:mm','FR-fr')
end

Hayton

Answer 12

How to get the First and Last Record time different in sql server....

....

Select EmployeeId,EmployeeName,AttendenceDate,MIN(Intime) as Intime ,MAX(OutTime) as OutTime,
DATEDIFF(MINUTE, MIN(Intime), MAX(OutTime)) as TotalWorkingHours
FROM ViewAttendenceReport WHERE AttendenceDate >='1/20/2020 12:00:00 AM' AND AttendenceDate <='1/20/2020 23:59:59 PM' 
GROUP BY EmployeeId,EmployeeName,AttendenceDate;

Eskisehir

Answer 13

If you want a notation of XX days YY hours and ZZ min, just try:

SELECT
    CAST(f.TimeAmount / 1440 AS VARCHAR(8)) + 'd ' +
    CAST((f.TimeAmount % 1440) / 60 AS VARCHAR(8)) + 'h ' +
    FORMAT(f.TimeAmount % 60, 'D2') + 'min' AS [TIME_TEXT]
FROM
    MyTable f

Scalenus

Answer 14

declare @TimeFormat  nvarchar(20),@minutes BIGINT
SET @minutes=125

SET @TimeFormat = 
    CASE WHEN @minutes >= 60 THEN (SELECT 
        CASE WHEN CAST((@minutes / 60) AS VARCHAR(2))<10 THEN '0' + CAST((@minutes / 60) AS VARCHAR(2)) ELSE CAST((@minutes / 60) AS VARCHAR(2)) END 
        + ':' +  
        CASE WHEN (@minutes % 60) > 0 
             THEN CASE WHEN  CAST((@minutes % 60) AS VARCHAR(2))<10 THEN '0'+CAST((@minutes % 60) AS VARCHAR(2)) ELSE CAST((@minutes % 60) AS VARCHAR(2)) END
             ELSE '00'
        END)
    ELSE CASE WHEN  CAST((@minutes % 60) AS VARCHAR(2))<10 THEN '00:0'+CAST((@minutes % 60) AS VARCHAR(2)) ELSE '00:'+CAST((@minutes % 60) AS VARCHAR(2)) END
    END

    select @TimeFormat

Supporter

this will show you whole minutes in a proper time format like hh:mm – Supporter

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Murrah