CONVERT from minutes to hours

Question

CONVERT from minutes to hours

2Tall

SSChampion

Points: 14632
More actions
September 25, 2008 at 5:56 am

#198572

Hi. I have a field 'Quantity' that is of data type DECIMAL (17,5). It holds a value or time in minutes.
I would like to display this as hours.
Would I use CAST / CONVERT within the SELECT or declare a variable?
DECLARE @TimeBooking decimal (17,5)
SET @TimeBooking = ??
so:
SELECT Id,P.PId,Users.FirstName + ' ' + Users.LastName AS UserName,LT.Quantity
FROM MyTables
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 15 posts - 1 through 14 (of 14 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

If you have minutes, then divide by 60 for the hours and mod 60 for the minutes left on the remaining hour... Though, what does your minutes look like with five decimals..?

selectquantity / 60 as hours,

quantity % 60 as minutes

from .....

/Kenneth

2Tall SSChampion Points: 14632 More actions · Answer 2

Hi. If a user inputs 8 the the field holds the value as 480:00000.If user enters 15 then 15:00000

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

So that means that the quantity column isn't correctly typed then, since the number of minutes doesn't have any fractions..

Still, divide the minutes by 60 and mod by 60 for the minutes in the remaining hour...

Is that what you're looking for?

2Tall SSChampion Points: 14632 More actions · Answer 4

Hi Kenneth. What do you mean by mod by minutes?

I implemened as:

LT.Quantity / 60 AS Hours

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

upperbognor Right there with Babe Points: 767 More actions · Answer 5

I think he is refering to

LT.Quantity % 60 AS Minutes

So if your quantity as 72 you could have

Hours Minutes

1 12

2Tall SSChampion Points: 14632 More actions · Answer 6

Thanks for filling me in on that.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Sorry for the late reply, but yes, that's precisely what I ment. 🙂

/ 60 for the full hour, and % 60 if you want the remaining minutes

/Kenneth

2Tall SSChampion Points: 14632 More actions · Answer 8

Hello again. So how would the full code read to get 1 Hour 10 minutes?

I have:

LT.Quantity / 60 AS Hours

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

upperbognor Right there with Babe Points: 767 More actions · Answer 9

SELECT

Case

When LT.Quantity/60 = 1 Then

Convert(nvarchar(10),LT.Quantity/60) +' Hour'

Else Convert(nvarchar(10),LT.Quantity/60) +' Hours'

End + ' ' +

Case

When LT.Quantity%60 = 1 Then

Convert(nvarchar(2),LT.Quantity%60)+' Minute'

Else Convert(nvarchar(2),LT.Quantity%60)+' Minutes'

End As [Time]

I only have a case statement to handle the pural of the word hour or minutes, as saying 1 hours doesn't sound as nice as saying 1 hour.

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

Philip Horan (9/25/2008)
Hi. If a user inputs 8 the the field holds the value as 480:00000.If user enters 15 then 15:00000
Thanks,
Phil.

Are you absolutely sure of this, Phil?

^{“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

2Tall SSChampion Points: 14632 More actions · Answer 11

Chris poor explanation on my part. If the user enters 8 in the hours field then enters 15 in the minutes field this is recorded as 75:00000.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

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

Philip Horan (9/29/2008)
Chris poor explanation on my part. If the user enters 8 in the hours field then enters 15 in the minutes field this is recorded as 75:00000.
Phil.

Many thanks for the explanation Phil, had me scratching me head for hours! It all makes sense now.

Cheers

ChrisM

^{“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

sgmunson SSC Guru Points: 110639 More actions · Answer 13

Ok, I'm completely confused... The last time I checked, 8 hours and 15 minutes is a total of 495 minutes, not 75. If the user enters 1 for the hours field and 15 in the minutes field, THEN you could come up with 75:00000 as a result, but I'm even confused by the presence of the ":", as a decimal number should be using a decimal point as opposed to a colon, right? (or is this a non-US environment?)

Steve

(aka smunson)

:):):)

Philip Horan (9/29/2008)
Chris poor explanation on my part. If the user enters 8 in the hours field then enters 15 in the minutes field this is recorded as 75:00000.
Phil.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

2Tall SSChampion Points: 14632 More actions · Answer 14

OK, having a blonde moment, what I should have posted was 1 hour 15 minutes = 75 🙂

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper