SQL Server Sum Minutes in to Hours and Minutes when its over 24 Hours

Hello Experts Exchange
I have a database that has a field that has a duration of time in minutes, I want to display these minute into Hours and minutes, but I have data that is over 24 hours how do I get SQL to display that?

So for example.  I want the following minutes to be displayed as the following Hours and Minutes.
Minutes                    Hours and Minutes
60                              01:00
1245                          20:45
7680                          128:00

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
select convert(varchar(10),sum([Minutes])/60) + ':' + right('0' + convert(varchar(2), sum([Minutes]) % 60),2) as [Hours and Minutes]
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
select convert(varchar(10),[Minutes]/60) + ':' + right('0' + convert(varchar(2), [Minutes] % 60),2) as [Hours and Minutes]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I had to pull this off for an airline that wanted to add all of the in-flight times, something like...
CREATE TABLE #tmp (minutes int) 

INSERT INTO #tmp (minutes) 
VALUES (60), (1245), (7680) 

SELECT DATEADD(mi, minutes, 0)
FROM #tmp

SELECT CAST(DATEDIFF(hour,  0, DATEADD(mi, minutes, 0)) as varchar(10)) + ':' + RIGHT('0' + CAST(minutes % 60 as varchar(2)), 2)
FROM #tmp

Open in new window


Since you're counting days as additional hours, this forces the use of varchar instead of time.
0
 
SQLSearcherAuthor Commented:
Hello
How do I then sum up with this query?

Regards

SQLSearcher
0
 
LowfatspreadCommented:
select case when minutes > 599 then '' else '0' end +convert(varchar(10),[Minutes]/60) + ':' + right('0' + convert(varchar(2), [Minutes] % 60),2) as [Hours and Minutes]
,....
from (select ......,sum(minutes) as minutes from .... ) as x
order by ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.