Comparing 2 float values in sqlserver

Question

I have a float variable-field1 in 2 tables-table1 & table2. When I query the table and check the values of the field both look identical but when I find their difference it gives a difference instead of zero.

Field1(Table1) value---84.4660194174757
Field2(Table2) value---84.4660194174757

Differnce---1.4210854715202E-14

Why would I get this problem?

The data is exported from a spreadsheet and sqlserver automatically detects it with FLOAT.. — user1050619, Apr 22 '13 at 14:46
`FLOAT` is an approximate datatype, so this kind of difference can be expected — Lamak, Apr 22 '13 at 14:47

score 28 · Answer 1 · answered Apr 22 '13 at 14:51

28

Use ROUND to limit the decimal places
Use ABS(value1-value2) < 0.00001 with some suitable value
Don't use float

answered Apr 22 '13 at 14:51

gbn

422,506
82
585
676

Are you one of the founders of this site? I ask because your rep is so high! – whatwhatwhat Feb 12 '16 at 19:48
3

It's worth noting that `Round` of a `float` returns a `float`, so the result remains an approximate numeric type. Another alternative is to `cast` or `convert` to an exact numeric type, e.g. [decimal](https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017), for the calculation. In any event the user needs to be aware of the _precision_ and _scale_ needed to represent values in their problem domain. – HABO Nov 07 '18 at 22:41
Is there a way to INSERT/SELECT the 4 binary bytes of a float? Work with it losslessly? I understand using @parameters is lossless. – Danny Holstein Feb 26 '22 at 23:46

Comparing 2 float values in sqlserver

1 Answers1

Linked