?
Solved

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

Posted on 2015-01-21
5
Medium Priority
?
574 Views
Last Modified: 2015-01-22
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
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40562038
select convert(varchar(10),[Minutes]/60) + ':' + right('0' + convert(varchar(2), [Minutes] % 60),2) as [Hours and Minutes]
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40562054
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
 

Author Comment

by:SQLSearcher
ID: 40562072
Hello
How do I then sum up with this query?

Regards

SQLSearcher
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40562076
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40562094
select convert(varchar(10),sum([Minutes])/60) + ':' + right('0' + convert(varchar(2), sum([Minutes]) % 60),2) as [Hours and Minutes]
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question