October 15, 2014 at 12:36 pm
Hi.. am running the below sql statement on a database...
select
c.MID,
c.RecordDate,
sum( c.A ) as A_unrounded,
round( sum( c.A ) , 3) as A_round_3,
round(9956.5875, 3) as same_value_rounded_manually
from
count.dbo.DispositionCodeTb A,
count.dbo.ProductTypeTb B,
count.dbo.CompletionMonthlyDispTb C
where
a.PID = b.ID
and a.ID = c.DispositionCode
and b.ID = c.ProductType
and sCode = 'O1'
and c.MID in ( 1336 )
and convert(date, c.recordDate, 101 ) = '2013-10-01'
group by c.MID, c.RecordDate
and result I get is
M ID RecordDateA_unroundedA_round_3 same_value_rounded_manually
1336 2013-10-01 9956.58759956.587 9956.588
MY question here is my DB is using data type float and I understand that is an approximate number and therefore problematic. But why is the rounding of .5875 coming out as .587 instead of .588?...Can someone explain me this please?
October 15, 2014 at 12:58 pm
From http://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
October 15, 2014 at 1:00 pm
Because FLOAT is not precise, it could actually be stored as 9956.587499999, and is displaying 9956.5875 so ROUND would leave it at .587 not rounding up to .588
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2014 at 1:08 pm
Just to add what Jack and Batgirl already said. Here's a little test that you can make. If you want to have an exact rounding, you need to use an exact data type like decimal.
CREATE TABLE FloatTest(
myfloat float)
INSERT INTO FloatTest
VALUES(9956.5875)
SELECT myfloat, ROUND( myFLoat, 3), ROUND( CAST( myfloat AS decimal( 18, 4)), 3)
FROM FloatTest
DROP TABLE FloatTest
October 15, 2014 at 1:19 pm
Thanks batgirl, Jack n Luis. Appreciate your responses.
October 16, 2014 at 1:54 pm
Hmm. "exact rounding" isn't (since the purpose of rounding is to NOT use the exact value).
Just be careful with the "rule of thumb". You want to actually be aware of what the data is that you're representing, and how you plan to use it. Exact data type might be awesome for final results, but if what you're looking at is already an approximate value or is logically an "intermediate value", it may not be accurate to treat every digit as significant. In some of those cases - the "exact type" will be rounding/truncating intermediate steps when it isn't appropriate to do so.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply