March 22, 2011 at 1:37 am
My answer is like this
first part which is i 've answered:
Declare @value1 decimal(20,10),@value2 decimal(20,3)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1 * @value2
Second part is screened answer:
DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1 * @value2
Ans:
The screened answer is u 've declared value2 decimal(30,13)... why u need like that?
my answer is Declare @value1 decimal(20,10),@value2 decimal(20,3)...... this is enough in SQL server 2005
March 22, 2011 at 3:11 am
pksutha (3/22/2011)
My answer is like thisfirst part which is i 've answered:
Declare @value1 decimal(20,10),@value2 decimal(20,3)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1 * @value2
Second part is screened answer:
DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1 * @value2
Ans:
The screened answer is u 've declared value2 decimal(30,13)... why u need like that?
my answer is Declare @value1 decimal(20,10),@value2 decimal(20,3)...... this is enough in SQL server 2005
That's kind of the "moral" of the question. Never use a "bigger" decimal/numeric than you actually need.
March 22, 2011 at 4:04 am
Duncan Pryde (3/21/2011)
michael.kaufmann (3/21/2011)
tilew-948340 (3/20/2011)
[...][...]
Excellent explanation. Couldn't have put it better myself.
Thank you very much for your kind words of appreciation, Duncan.
Thanks again for your question and great explanation.
Regards,
Michael
March 22, 2011 at 4:25 am
Duncan Pryde (3/22/2011)
That's kind of the "moral" of the question. Never use a "bigger" decimal/numeric than you actually need.
The moral I've drawn is that maybe Floats aren't as bad as we thought...
March 22, 2011 at 6:30 am
Declare @value1 numeric(38,10)
Declare @value2 numeric(1,1)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1
SELECT @value2
SELECT @value1 * @value2
= "123456789.012345679"
March 22, 2011 at 6:56 am
rlswisher (3/22/2011)
Declare @value1 numeric(38,10)Declare @value2 numeric(1,1)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1
SELECT @value2
SELECT @value1 * @value2
= "123456789.012345679"
As expected.
Result precision is 38+1+1 = 40, scale is 10+1 = 11. Max allowed precision is 38, so precision and scale are reduced by 2, giving a final result precision and scale of 38,9 - which is why the result is rounded as you can see.
March 22, 2011 at 7:51 am
March 22, 2011 at 8:05 am
Excellent question and nice explanation..
Learned something new that Precision and Scale varies for the resulting value
based on (+, -, / , *, [UNION | EXCEPT | INTERSECT] , % ) .
Thanks for posting this...
March 22, 2011 at 8:25 am
great qeustion
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2011 at 8:38 am
Great question. You would not believe how long and how often programers get this wrong.
There is even a list that is maintained of ROM chips that do weight conversions incorrectly becuase Dec(6,2) is used instead of Dec(13,5)
I even had to "show the math" on this exact thing a year ago when I had to explain why the weight conversion code used to change Pound to Kilos and vice versa was wrong in every appliation where I work.
Not understanding this math is why so many ships have a problem balancing thier loads.
Sometimes the cargo is weighed in pounds and the balast program uses Kilos.
Then someone uses a cheap hand calculator to convert the 100,000 tons in pounds to Kilos and the weghts off by at least 1,000 tons.
March 22, 2011 at 8:47 am
SanDroid (3/22/2011)
Great question. You would not believe how long and how often programers get this wrong.There is even a list that is maintained of ROM chips that do weight conversions incorrectly becuase Dec(6,2) is used instead of Dec(13,5)
I even had to "show the math" on this exact thing a year ago when I had to explain why the weight conversion code used to change Pound to Kilos and vice versa was wrong in every appliation where I work.
Not understanding this math is why so many ships have a problem balancing thier loads.
Sometimes the cargo is weighed in pounds and the balast program uses Kilos.
Then someone uses a cheap hand calculator to convert the 100,000 tons in pounds to Kilos and the weghts off by at least 1,000 tons.
Are you sure that extra weight isn't from the stowaways (Illegal aliens) that are aboard and unaccounted for?
Besides, isn't counting ballast by kilo's easier? One kilo of weight and one liter of water displacement equals neutral buoyancy. Doing that in another factor is 2.2 pounds for each 0.26417205263729593 (approx) US gallons of displacement.
March 22, 2011 at 9:15 am
cengland0 (3/22/2011)
SanDroid (3/22/2011)
Besides, isn't counting ballast by kilo's easier? One kilo of weight and one liter of water displacement equals neutral buoyancy. Doing that in another factor is 2.2 pounds for each 0.26417205263729593 (approx) US gallons of displacement.
I was talking about when the pounds are converted to Kilos before calculating displacement, since Kilos is the way to go, the math is always done wrong.
What you are trying to point out...:-P That you know why we use Kilo's in the Balast programs?
March 22, 2011 at 11:43 am
SanDroid (3/22/2011)
I was talking about when the pounds are converted to Kilos before calculating displacement, since Kilos is the way to go, the math is always done wrong.What you are trying to point out...:-P That you know why we use Kilo's in the Balast programs?
Just that I would never use pounds in the formula.
Also, when speaking of Tons, you have the metric ton which is 1000 kilograms and that is about 2205 pounds. Most people assume ton as a "short ton" which is 2000 pounds. As you can see, there's a couple hundred pounds difference between the two so it might not be a conversion issue but an assumed unit problem.
March 26, 2011 at 9:19 am
cengland0 (3/22/2011)
SanDroid (3/22/2011)
I was talking about when the pounds are converted to Kilos before calculating displacement, since Kilos is the way to go, the math is always done wrong.What you are trying to point out...:-P That you know why we use Kilo's in the Balast programs?
Just that I would never use pounds in the formula.
Also, when speaking of Tons, you have the metric ton which is 1000 kilograms and that is about 2205 pounds. Most people assume ton as a "short ton" which is 2000 pounds. As you can see, there's a couple hundred pounds difference between the two so it might not be a conversion issue but an assumed unit problem.
I've heard of short tons before, but never seen a case where they have been used (maybe that's because I don't live where people do USA measures). A metric ton (tonne) is 1000kg, and a long ton is 2240lb, so the difference between the two commonly used tons is only about 1.5%, not the 10% difference between the short ton and the tonne or the 12% difference between the short ton and the long ton. This means that the error mentioned (something over 1000 tons in 100000, so a bit over 1%) is far too big to have been cause by confusing short tons and metric tonnes and although it's about the right size for confusing metric tonnes and long tons that seems very unlikely to me because Sandroid wrote explicitly about conversion from pounds to kilograms, so it really is rounding error not terminological confusion.
Tom
April 1, 2011 at 12:16 am
Interesting question and a great explanation.
I learned something, although i got it wrong :)!
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply