September 27, 2019 at 5:38 am
Hi
I have a condition where I want to compare float value in my code and depending on that do some operations.
The data type is Money.
Now the problem is float value differs on each machine 32 bit and 64 bit.
How do i write my code to the precision that the float condition can be met?
like on my machine floatval>0.001 passes
but on my clients machine it fails.
September 27, 2019 at 7:12 am
I think the real question is why you are using float.
float is an approximation, comparing it to a fixed decimal type (such as money) is always going to have issues.
can you convert your float columns to a decimal(x,y) type column?.... that would fix the error
MVDBA
September 27, 2019 at 9:02 am
--
-- Float compare.
-- Compare two numbers which are 'almost' equal.
--
DECLARE @A FLOAT = 1./3
DECLARE @B FLOAT = .33 -- Change this number with more repetition of the number 3
DECLARE @FACTOR FLOAT = 1 -- Change this number to see how this changes the absolute difference but not the relative difference.
SET @A = @A * @FACTOR
SET @B = @B * @FACTOR
PRINT @A
PRINT @B
-- Relative Error
IF ABS((@A-@B)/@A) < .001 PRINT 'RELATIVE OK' ELSE PRINT 'RELATIVE NOT OK'
-- Absolute Error
IF ABS((@A-@B)) < .001 PRINT 'ABSOLUTE OK' ELSE PRINT 'ABSOLUTE NOT OK'
Floats should never be compared with = OR <> tests.
Depending how the data is processed floats can have slight differences. The same goes for datetime format's. I'll explain using datetime. The time 08:00 is stored differently in different systems, in Excel it is stored in an approximate format as digitally very close to 1/3 of a day. In SQL server it is stored exactly as 08:00. In Oracle it is stored exactly.
The value 1/10 is stored exactly in Oracle in a number, Excel and in SQL-server it is stored close to digitally 1/10 but not exactly.
In Money 1/10 is stored exactly in Oracle and in SQL-server but not in Excel.
Depending on de conversions and the operations, these float numbers do not have the same value in the end, this depends on precision during conversions and on storing formats.
So for a given situations you can use the absolute difference, for example when measuring, you could determine that 10 mm is accurate enough for land surveying. But in machine tooling the accuracy must by better than 0.01 mm.
But a more general solution is the relative difference, for machine tooling this could be 1 in a 100 000. But for land surveying if done on a 'world' scale 1 in a 100 000 is not accurate enough. (40 000 km circumference of the earth 1/100 000 is still 400 meters which is two streets away).
So you have to choose the method (relative or absolute) and the accuracy depending on the goal.
Play around with the values in the example to get a feel for what you want.
Ben
September 27, 2019 at 9:36 am
I'd also avoid using Money data type if you can
especially if you are using international currencies.
money only supports 2 decimal places, but there are lots of currencies that support 3 (Bahrain,tunisia,iran for example) or 4 decimal places (Chilean Unidad de Fomento)
and if you start putting large amounts of currency in Vietnamese dong then you run into overflows very quickly.
MVDBA
September 30, 2019 at 7:22 am
--
-- Money is exact to 4 decimal places.
--
Declare @geld money = 12.3456
SELECT @geld
SET @geld = @geld*0.1
SELECT @geld
The mony type is exact to 4 decimal places, it get's rounded during calculations to 4 decimal places. So smaller fragments than 1/10000 of the currency can not be represented. So if you buy something like 17 noddles for 1 unit of currency, you dan not exactly represent the value of one noddle.
Be carefull using the decimal system, 1/10 and 1/100 can not be represented in floats. With accountancy where every cent has to be exact this can be a problem. Rounding of will result in incorrect bookkeeping. When rounded of to the nearest 1/100 it often works out, but it is never exact.
4 decimal places should be enough for any (current) currency to represent the smallest denomination.
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply