SQL Server Floating Point in WHERE Clause

Question

I'm trying to query a database, I need to get a list of clients where their weight is 60.5. The problem is that 60.5 is real, I have never requested a database with real in the where clause before.

I tried this:

SELECT Name FROM Customers WHERE Weight=60.5
SELECT Name FROM Customers WHERE Weight=cast(60.5 as real)
SELECT Name FROM Customers WHERE Weight=cast(60.5 as decimal)
SELECT Name FROM Customers WHERE Weight=convert(real,'60.5')
SELECT Name FROM Customers WHERE Weight=convert(decimal,'60.5')

These queries return 0, but in the Customers table there are 10 rows with weight = 60.5

Answer 1

Your problem is that floating point numbers are inaccurate by definition. Comparing what seems to be 60.5, the letter 60.5 may not work as you noticed.

, , , epsilon, :

SELECT Name FROM Customers WHERE ABS(Weight-60.5) < 0.001

:

SELECT Name FROM Customers WHERE Weight BETWEEN 64.999 AND 65.001

Answer 2

, DECIMAL. , .

@ , . ABS(Weight-60.5) < 0.001 . DECIMAL, Weight=60.5 .