# Floating point problems

• ### Question

• Hi! I am writing some functions in Server 2005 and i'm facing some weird problems, could somebody explain me whats happening ??

I wrote the next code where i declare as float variable that should increase by .1 units in every cycle of the loop, and when I check the output notice that in the 60th cycle mi counter has increased .0001 : there is not so little numbers or a great number of cycles, why is this happening???

Is there any solution ??? other than update my version because my hardware is limted

```/*

declare @medhb float(15)
set @medhb = 4.5

WHILE @medhb <= 18

BEGIN

print STR(@medhb,10,5)

set @medhb = @medhb + .1000

CONTINUE

END

*/OUTPUT:  10.30000  10.40001  10.50001  10.60001
```

Thursday, June 13, 2013 9:56 PM

• No - this does not "solve" the problem.  If you select @medhb inside the loop, you will see that the actual value is very close but not exactly the desired value.  For example, 7.9 is actually 7.89999999999999.  Both you and OP have unintentionally hidden the approximate nature of floating point numbers by using STR function to display the value - which rounds while formatting (as we should expect).

The moral of the story is that you need to use the correct datatype and understand the limitations of your choice. If the requirement is exact precision, then the more appropriate datatype is probably decimal / numeric.

A more complete explanation of floating point usage in a computer in all its gory detail.

Floating point arithmetic

• Edited by Friday, June 14, 2013 7:04 PM added floating point link
• Proposed as answer by Monday, June 17, 2013 4:04 AM
• Marked as answer by Saturday, June 22, 2013 3:09 AM
Friday, June 14, 2013 6:59 PM
• Hi,

You shouldn't be using FLOATs for that kind of tasks.

FLOATs are intended for scientific and statistical use, not storing counters, integers or currency amounts.

For your particular case, NUMERIC or DECIMAL variables are best

DECLARE @medhb NUMERIC(15,4)

SET @medhb = 4.5

...

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, June 14, 2013 7:07 PM
• Use fixed decimal datatype instead of float.

```declare @medhb decimal(15,4)
set @medhb = 4.5
WHILE @medhb <= 18
BEGIN
print STR(@medhb,10,5)
set @medhb = @medhb + .1000
CONTINUE
END```

Friday, June 14, 2013 7:21 PM

### All replies

• this happens regardless the value used as n in float(n)
Thursday, June 13, 2013 9:58 PM
• Dear Avila,

Based on BOL, Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Syntax: float [ ( n ) ]

--If n is specified, it must be a value between 1 and 53. The default value of n is 53.

If you change value of n to 25 through 53, the precision will change to 15 digits and your problem will solve. Try this please:

```DECLARE @medhb FLOAT(25)
SET @medhb = 4.5

DECLARE @inc FLOAT(5)
WHILE @medhb <= 18
BEGIN

PRINT STR(@medhb, 10, 5)

SET @medhb = @medhb + .1000

CONTINUE

END```

Regards,

Saeid

http://sqldevelop.wordpress.com/

Thursday, June 13, 2013 10:31 PM
• First of all we do not write loops in SQL; this is a declarative language. Next, you need to google how IEEE floating point works. This is part of freshman programming classes that deal with approximate numerics. Today, SQL programmers use DECIMAL(s,p); we are not limited to 16  bit hardware.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Friday, June 14, 2013 1:32 AM
• No - this does not "solve" the problem.  If you select @medhb inside the loop, you will see that the actual value is very close but not exactly the desired value.  For example, 7.9 is actually 7.89999999999999.  Both you and OP have unintentionally hidden the approximate nature of floating point numbers by using STR function to display the value - which rounds while formatting (as we should expect).

The moral of the story is that you need to use the correct datatype and understand the limitations of your choice. If the requirement is exact precision, then the more appropriate datatype is probably decimal / numeric.

A more complete explanation of floating point usage in a computer in all its gory detail.

Floating point arithmetic

• Edited by Friday, June 14, 2013 7:04 PM added floating point link
• Proposed as answer by Monday, June 17, 2013 4:04 AM
• Marked as answer by Saturday, June 22, 2013 3:09 AM
Friday, June 14, 2013 6:59 PM
• Hi,

You shouldn't be using FLOATs for that kind of tasks.

FLOATs are intended for scientific and statistical use, not storing counters, integers or currency amounts.

For your particular case, NUMERIC or DECIMAL variables are best

DECLARE @medhb NUMERIC(15,4)

SET @medhb = 4.5

...

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, June 14, 2013 7:07 PM
• Use fixed decimal datatype instead of float.

```declare @medhb decimal(15,4)
set @medhb = 4.5
WHILE @medhb <= 18
BEGIN
print STR(@medhb,10,5)
set @medhb = @medhb + .1000
CONTINUE
END```

Friday, June 14, 2013 7:21 PM