CODEWITHSUNDEEP
sqlsql-serverfloating-point
flofreelance
flofreelance

Reputation: 944

SQL Server Floating Point in WHERE clause

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

Upvotes: 8

Views: 17112

Answers (2)

Amit
Amit

Reputation: 46323

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

Upvotes: 20

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

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.

Upvotes: 2

Related Questions