Comparing Float Values: Why 0.1 + 0.2 Is Not 0.3 in SQL Server

Comparing float values with an equals sign is a bet you will eventually lose. The classic proof is 0.1 + 0.2, which does not equal 0.3 once float gets involved. Let me show you the exact digits, where it hurts in real queries, and two clean ways out.

The surprise, in one query

A manager once asked me why a total was off by a hair, and why a lookup for an exact amount found nothing. Same cause both times. Here is the smallest version I know. Watch the first column closely.

DECLARE @a float = 0.1, @b float = 0.2;
SELECT CASE WHEN 0.1 + 0.2 = 0.3 THEN 'equal' ELSE 'not equal' END AS PlainLiterals,
       CASE WHEN @a + @b = 0.3 THEN 'equal' ELSE 'not equal' END AS FloatVariables;

Plain literals say equal. Float variables say not equal. Why the difference? In T-SQL, a number like 0.1 typed in a query is an exact decimal, not a float. Only when you store it in a float column or variable does it become an approximation. So the trap hides until real data arrives.

See the digits float hides

Float keeps binary fractions. The value 0.1 has no exact binary form, just like one third has no exact decimal form. Many tools round float on screen, so the damage can look invisible. Ask for all 17 digits and it shows up.

DECLARE @a float = 0.1, @b float = 0.2;
SELECT CONVERT(varchar(30), @a + @b, 3) AS SumDigits,
       CONVERT(varchar(30), CONVERT(float, 0.3), 3) AS ThreeTenths;

Style 3 prints 17 digits in scientific form, and e-001 just means times 0.1. The sum is 0.30000000000000004. The stored 0.3 is 0.29999999999999999. Two values that print alike on a rounded screen are not the same value, so the equals test says no.

Where it bites in real queries

Two places. First, an equality filter on a float column quietly finds nothing. Second, sums drift when you add the same small amount many times. Here is one row stored both ways, and then ten tenths added up.

DROP TABLE IF EXISTS #Readings;
CREATE TABLE #Readings (Id int PRIMARY KEY, AsFloat float NOT NULL, AsDecimal decimal(9,1) NOT NULL);
INSERT #Readings VALUES (1, CONVERT(float, 0.1) + CONVERT(float, 0.2), 0.1 + 0.2);
SELECT (SELECT COUNT(*) FROM #Readings WHERE AsFloat = 0.3) AS FloatMatches,
       (SELECT COUNT(*) FROM #Readings WHERE AsDecimal = 0.3) AS DecimalMatches,
       (SELECT COUNT(*) FROM #Readings WHERE ABS(AsFloat - 0.3) < 0.000001) AS ToleranceMatches,
       (SELECT COUNT(*) FROM #Readings WHERE ROUND(AsFloat, 6) = 0.3) AS RoundedMatches;

The float column finds 0 rows. The decimal column finds 1. The tolerance test finds 1 as well, and so does rounding the float to six places first. I will explain both in a moment.

DROP TABLE IF EXISTS #Tenths;
CREATE TABLE #Tenths (Id int PRIMARY KEY, AsFloat float NOT NULL, AsDecimal decimal(9,1) NOT NULL);
INSERT #Tenths SELECT value, 0.1, 0.1 FROM GENERATE_SERIES(1, 10);
SELECT CONVERT(varchar(30), SUM(AsFloat), 3) AS FloatSum,
       SUM(AsDecimal) AS DecimalSum,
       CASE WHEN SUM(AsFloat) = 1 THEN 'equal' ELSE 'not equal' END AS FloatEqualsOne
FROM #Tenths;

Ten tenths should be exactly 1. The decimal column gets there. The float column does not, and the equals test against 1 says so. That is how a report ends up one hair short of its own control total.

Two clean ways out

The first fix is a tolerance. Subtract the two values, take the absolute value and ask if the gap is tiny. That is the ABS test in the table above, and it is why the third count found its row. Pick a tolerance that fits your data. A thousandth is fine for some measurements and absurd for others. Rounding both sides to the same number of places works too, as the fourth count shows. Just remember that you must do it on every query. One forgotten filter and the old problem is back, which is the real argument for decimal.

The second fix is better when you control the design: store exact quantities as decimal. Money, quantities, rates and anything a person will type and expect back belong in decimal. Keep float for measurements that are already approximate, such as sensor readings or scientific values.

One more trap. A real is a smaller float, and it rounds differently. Compare the two below and they disagree, even though both came from 0.1.

SELECT CASE WHEN CONVERT(real, 0.1) = CONVERT(float, 0.1) THEN 'equal' ELSE 'not equal' END AS RealVersusFloat;

DROP TABLE IF EXISTS #Tenths;
DROP TABLE IF EXISTS #Readings;

Next time an exact match on a decimal-looking number fails, check the column type before you blame the data.

A float is not a precise number, it is a very good guess.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.