How to convert kilograms to pounds and ounces

Question

Post reply

How to convert kilograms to pounds and ounces

NineIron

SSChampion

Points: 12537
More actions
December 29, 2017 at 7:58 am

#330656

I'm need to convert kilograms to pounds and ounces. See attached data.
create table #T
(
ID,
Weight decimal(10,2)
)
insert into #T(ID,PtWeight) values(1,58.3)
insert into #T(ID,PtWeight) values(2,45.3)
insert into #T(ID,PtWeight) values(3,64.5)
insert into #T(ID,PtWeight) values(4,52.2)
insert into #T(ID,PtWeight) values(5,63.2)
insert into #T(ID,PtWeight) values(6,65.4)
insert into #T(ID,PtWeight) values(7,71.2)
insert into #T(ID,PtWeight) values(8,85.6)
insert into #T(ID,PtWeight) values(9,25.5)
insert into #T(ID,PtWeight) values(10,26)
insert into #T(ID,PtWeight) values(11,45.9)
insert into #T(ID,PtWeight) values(12,76.4)

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Multiply by 2.2 to convert to pounds and multiply the resulting decimal part by 16 to get the ounces.

John

ScottPletcher SSC Guru Points: 101121 More actions · Answer 2

Or perhaps multiply by 2.2046226218, or some shorter variant of that, if you need more accuracy. 2.2 is rather rough estimate.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

ScottPletcher - Friday, December 29, 2017 8:18 AM
Or perhaps multiply by 2.2046226218, or some shorter variant of that, if you need more accuracy. 2.2 is rather rough estimate.

Understood. I thought of looking up the exact number, but then I figured that the original poster is just as capable of doing that as I am.

John

ScottPletcher SSC Guru Points: 101121 More actions · Answer 4

John Mitchell-245523 - Friday, December 29, 2017 8:23 AM
ScottPletcher - Friday, December 29, 2017 8:18 AM
Or perhaps multiply by 2.2046226218, or some shorter variant of that, if you need more accuracy. 2.2 is rather rough estimate.
Understood. I thought of looking up the exact number, but then I figured that the original poster is just as capable of doing that as I am.
John

Idk, if OP was willing to do that, would prob never have asked the q to begin with 😀

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

NineIron SSChampion Points: 12537 More actions · Answer 5

Thanx but, how can I get the decimal part to multiply by 16. I think it has something to do with "%".

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

No, just subtract the integer part, which you get by using FLOOR.

John

NineIron SSChampion Points: 12537 More actions · Answer 7

Given a weight of 68.6 kg. Is this the best way to do it?

selectconcat(floor((68.6*2.2)),' lbs ',((68.6*2.2)%1)*16,'oz') as PtWeight

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

You don't want to do the calculation twice if you don't have to. Try this:

SELECT CONCAT(F.FloorWeight,'lbs ',(W.WeightinPounds - F.FloorWeight)*16,'oz') as PtWeight FROM #T t CROSS APPLY ( SELECT t.PtWeight * 2.2 ) W(WeightinPounds) CROSS APPLY ( SELECT FLOOR(W.WeightinPounds) ) F(FloorWeight)

John

NineIron SSChampion Points: 12537 More actions · Answer 9

NineIron

SSChampion

Points: 12537

December 29, 2017 at 10:05 am

#1973861

Thanx. Happy New Year.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

NineIron - Friday, December 29, 2017 8:48 AM
Given a weight of 68.6 kg. Is this the best way to do it?
selectconcat(floor((68.6*2.2)),' lbs ',((68.6*2.2)%1)*16,'oz') as PtWeight

suggest you do as Scott suggested...increase accuracy....you are 5 ounces adrift

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

Much easier solution is to simply weigh it on a pounds scale. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

J Livingston SQL - Friday, December 29, 2017 10:48 AM
NineIron - Friday, December 29, 2017 8:48 AM
Given a weight of 68.6 kg. Is this the best way to do it?
selectconcat(floor((68.6*2.2)),' lbs ',((68.6*2.2)%1)*16,'oz') as PtWeight
suggest you do as Scott suggested...increase accuracy....you are 5 ounces adrift

More like 11 ounces adrift...

If you do the conversions using ounces to start with, the formulas become extremely symmetrical. Format it as you desire.

SELECT FLOOR(68.6*35.27396195/16), 68.6*35.27396195%16

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Jeff Moden - Friday, December 29, 2017 7:35 PM
J Livingston SQL - Friday, December 29, 2017 10:48 AM
NineIron - Friday, December 29, 2017 8:48 AM
Given a weight of 68.6 kg. Is this the best way to do it?
selectconcat(floor((68.6*2.2)),' lbs ',((68.6*2.2)%1)*16,'oz') as PtWeight
suggest you do as Scott suggested...increase accuracy....you are 5 ounces adrift
More like 11 ounces adrift...
If you do the conversions using ounces to start with, the formulas become extremely symmetrical. Format it as you desire.
SELECT FLOOR(68.6*35.27396195/16), 68.6*35.27396195%16

Actually no, Jeff. It's about 5.07 oz out, which is much closer to the 5 oz J Livingstone suggested than to your 11. When I first read your comment, I thought there must be some crazy side-effect of the horrible truncation in decimal arithmetic because 2.2 lbs /kg is 35.2oz/kg and the error of about 0.074 oz/kg could only account for about being about 5 ox adrift. Then I looked at it a bit closer, and thought the number of operations and the number of decimal places are extremely low, that crazy truncation stuff can't have kicked in at all - so I decided the only answer was to run it - and your method delivers 151 pounds 3.793... oz while the method Nonelron asked about delivers 150 lbs 14.72 oz.
But I agree with you that it's generally cleaner to start with the smaller unit, so going for ounces first is good. And extra accuracy is definitely required unless answers are aceptable with a fifth of a percent error, so 35.27396195 is a good number to use (or 35.27396194958 for the really picky who have hardware and software supporting the latest decimal floating point standard, but I suppose none of us except some of the youngest are likely to see that).

Tom

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

TomThomson - Saturday, December 30, 2017 4:50 AM
Jeff Moden - Friday, December 29, 2017 7:35 PM
J Livingston SQL - Friday, December 29, 2017 10:48 AM
NineIron - Friday, December 29, 2017 8:48 AM
Given a weight of 68.6 kg. Is this the best way to do it?
selectconcat(floor((68.6*2.2)),' lbs ',((68.6*2.2)%1)*16,'oz') as PtWeight
suggest you do as Scott suggested...increase accuracy....you are 5 ounces adrift
More like 11 ounces adrift...
If you do the conversions using ounces to start with, the formulas become extremely symmetrical. Format it as you desire.
SELECT FLOOR(68.6*35.27396195/16), 68.6*35.27396195%16
Actually no, Jeff. It's about 5.07 oz out, which is much closer to the 5 oz J Livingstone suggested than to your 11. When I first read your comment, I thought there must be some crazy side-effect of the horrible truncation in decimal arithmetic because 2.2 lbs /kg is 35.2oz/kg and the error of about 0.074 oz/kg could only account for about being about 5 ox adrift. Then I looked at it a bit closer, and thought the number of operations and the number of decimal places are extremely low, that crazy truncation stuff can't have kicked in at all - so I decided the only answer was to run it - and your method delivers 151 pounds 3.793... oz while the method Nonelron asked about delivers 150 lbs 14.72 oz.
But I agree with you that it's generally cleaner to start with the smaller unit, so going for ounces first is good. And extra accuracy is definitely required unless answers are aceptable with a fifth of a percent error, so 35.27396195 is a good number to use (or 35.27396194958 for the really picky who have hardware and software supporting the latest decimal floating point standard, but I suppose none of us except some of the youngest are likely to see that).

It's my fault... I was incorrectly only looking at the number of ounces. Not sure why I didn't see the difference in pounds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)