SQL Server Floating Point in WHERE clause

Question

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

I've 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 values but in the Customers table their are 10 rows with Weight=60.5

Solution 1

Your problem is that floating point numbers are inaccurate by definition. Comparing what seems to be 60.5 to a literal 60.5 might not work as you've noticed.

A typical solution is to measure the difference between 2 values, and if it's smaller then some predefined epsilon, consider them equal:

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

For better performance, you should actually use:

SELECT Name FROM Customers WHERE Weight BETWEEN 64.999 AND 65.001

Solution 2

If you need equality comparison, you should change the type of the column to DECIMAL. Decimal numbers are stored and compared exactly, while real and float numbers are approximations.

@Amit's answer will work, but it will perform quite poorly in comparison to my approach. ABS(Weight-60.5) < 0.001 is unable to use index seeks. But if you convert the column to DECIMAL, then Weight=60.5 will perform well and use index seeks.