Converting minutes into HH:MM

Question

Post reply

Converting minutes into HH:MM

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
November 10, 2010 at 6:01 am

#242772

Hi There,
I think I have put this in the right place, please forgive me if not.
I have a query where it returns the average mins taken to complete a call. It returns the value in minutes but I just know I am going to get asked about it posibly being in HH:MM.
I had a look around the forum and have tried a few things but it's not working so far.
Here is my query:
Select sum(fix_time/60)/COUNT (*)
from DB
where suppgroup = 'XXX'
I am doing this as a stored procedure if that helps at all.
Thanks in advance for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Try this:

select cast(AvgMins/60 as varchar(10)) + ':' + right('00' + cast(AvgMins%60 as varchar(10)), 2)

from

(Select sum(fix_time/60)/COUNT (*) as AvgMins

from DB

where suppgroup = 'XXX') as Sub1;

The percent symbol gets the modulo (remainder) of a division operation. I pad the minutes with zeroes, and use "right" to only get the 2 characters I need). Then I convert them to strings, add a colon in the middle, and concatenate them together.

Does that get you what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT

OriginalMinutes= Minutes,

INTHours= Minutes/60,

INTRemainingMinutes = Minutes%60,

CHARHours= REPLACE(STR(Minutes/60,2), ' ', '0'),

CHARRemainingMinutes = REPLACE(STR(Minutes%60,2), ' ', '0'),

HoursAndMinutes= REPLACE(STR(Minutes/60,2), ' ', '0') + ':' + REPLACE(STR(Minutes%60,2), ' ', '0')

FROM (

SELECT 25 AS Minutes UNION ALL

SELECT 59 UNION ALL

SELECT 60 UNION ALL

SELECT 61 UNION ALL

SELECT 118 UNION ALL

SELECT 119 UNION ALL

SELECT 120 UNION ALL

SELECT 121

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 3

Thank you so much both of you, GSquared that is perfect I used your suggestion.

Thanks again.

GSquared SSC Guru Points: 260824 More actions · Answer 4

You're welcome.

Keep in mind that the math I did on it only works if the numbers are integers. Otherwise, you have to convert to integers before you divide, or you'll get rounding errors.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon