Common Error in float Data Type

Think about this query.

DECLARE @Cnt FLOAT;
SET @Cnt = 0
WHILE (@Cnt <> 1.0 )
BEGIN
  SET @Cnt = @Cnt+ 0.1
  PRINT @Cnt
END

you would expect it to complete after displaying 0.1 to 1.0. But see the output.

How come, what’s wrong here.

Problem here is with the float data type. 0.1 may not not be stored as exactly as 0.1 instead it will be stored 0.0999999.

How we can correct this. By changing the float (approximate data type) to numeric (5,1) (exact data type) will solve the problem.

DECLARE @Cnt NUMERIC(5,1);
SET @Cnt = 0
WHILE (@Cnt <> 1.0 )
BEGIN
  SET @Cnt = @Cnt+ 0.1
  PRINT @Cnt
END

Output for the above query is.

which is what we are looking for.