Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2015-01-21
5
Medium Priority
?
671 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
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

577 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