• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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


1 Solution
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]
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.
SQLSearcherAuthor Commented:
How do I then sum up with this query?


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 ...
Phillip BurtonDirector, 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]
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now