Tuesday, March 26, 2013

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.


image


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.


image



which is what we are looking for.

No comments:

Post a Comment