DovesandChicks DovesandChicks - 5 months ago 15
SQL Question

SQL query returning less than or equal to instead of less than?

Using Microsoft SQL Server management Studio I have the following query:

DECLARE @payo25 float
SET @payo25 = 7.2

SELECT first_name, surname, hourly_rate
FROM table1
WHERE hourly_rate < @payo25


This query returns:

Jane Smith 7.2
Fred Bloggs 4.9


I thought
<
meant less than, not less than or equal to?

Any ideas?

Answer

Floats are great for storing really big numbers where the trade off in precision is worth it to be able to store the really big number. If you aren't working with really big numbers, then use a more precise numeric data type like real or decimal or what have you.

What you'll find if you store 7.2 as a float, is that the actual number stored is something more like 7.20000000000000000000003. So, when you compare it to 7.2 you get back the strange result that your float 7.2 is greater than your real 7.2.

Comments