MS SQL Server - Floating point precision

Author

Floating point precision

MarkusJNZ@gmail.com

2006-10-24, 6:32 pm

Hi, I have a SP which adds a bunch of the same number together 365
times

The base number is 0.0575342465753425

I am storing this as a float but when I view it in the first iteration
it appears as

0.0575342

The next one is

0.115068

and the 10th one is

0.575342

and finally the 365 one is

21.0575

However if I multiply the original number by 365 I get the following
21.0000000000000125

Which is vastly different from the one I got using a float.

How can I get more precision using MSSQL float? Am I using the wrong
Datatype??

TIA
Mark
====================
=============
2006MJGOOGLENEWS

Uri Dimant

2006-10-24, 6:32 pm

Don't use FLOAT it is not accurate in terms of precision

What you get if you use DECIMAL datatype intead?

<MarkusJNZ@gmail.com> wrote in message
news:1159441537.669380.150810@b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
>
> The base number is 0.0575342465753425
>
> I am storing this as a float but when I view it in the first iteration
> it appears as
>
> 0.0575342
>
> The next one is
>
> 0.115068
>
> and the 10th one is
>
> 0.575342
>
> and finally the 365 one is
>
> 21.0575
>
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
>
> Which is vastly different from the one I got using a float.
>
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype??
>
> TIA
> Mark
> ====================
=============
> 2006MJGOOGLENEWS
>

Tracy McKibben

2006-10-24, 6:32 pm

MarkusJNZ@gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
>
> The base number is 0.0575342465753425
>
> I am storing this as a float but when I view it in the first iteration
> it appears as
>
> 0.0575342
>
> The next one is
>
> 0.115068
>
> and the 10th one is
>
> 0.575342
>
> and finally the 365 one is
>
> 21.0575
>
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
>
> Which is vastly different from the one I got using a float.
>
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype??
>
> TIA
> Mark
> ====================
=============
> 2006MJGOOGLENEWS
>

From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."

Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Hugo Kornelis

2006-10-24, 6:32 pm

On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@gmail.com wrote:

>Hi, I have a SP which adds a bunch of the same number together 365
>times
>
>The base number is 0.0575342465753425
>
>I am storing this as a float but when I view it in the first iteration
>it appears as
>
>0.0575342
>
>The next one is
>
>0.115068
>
>and the 10th one is
>
>0.575342
>
>and finally the 365 one is
>
>21.0575
>
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>
>Which is vastly different from the one I got using a float.
>
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype??

Hi Mark,

Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.

Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):

declare @flt float, @res float, @i int
set @flt = 0.0575342465753425
set @res = 0
set @i = 0
while @i < 365
begin
set @i = @i + 1
set @res = @res + @flt
end
select cast(@res as decimal(38,30))
select cast(@flt * 365.0 as decimal(38,30))

Results:

---------------------------------------
21. 00000000000004600000
0000000000

---------------------------------------
21. 00000000000001400000
0000000000

As you see, there is SOME loss of precision, but not quites as much as
you had.

Incidentally, if you change the datatypes of @flt and @res in the code
above to decimal(38,10), the results change to

---------------------------------------
21. 00000000000001250000
0000000000

---------------------------------------
21. 00000000000001250000
0000000000

--
Hugo Kornelis, SQL Server MVP

MarkusJNZ@gmail.com

2006-10-24, 6:33 pm

Thanks everyone for your help.

Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol

Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@gmail.com wrote:
>
>
> Hi Mark,
>
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
>
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
>
> declare @flt float, @res float, @i int
> set @flt = 0.0575342465753425
> set @res = 0
> set @i = 0
> while @i < 365
> begin
> set @i = @i + 1
> set @res = @res + @flt
> end
> select cast(@res as decimal(38,30))
> select cast(@flt * 365.0 as decimal(38,30))
>
> Results:
>
>
> ---------------------------------------
> 21. 00000000000004600000
0000000000
>
>
> ---------------------------------------
> 21. 00000000000001400000
0000000000
>
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
>
> Incidentally, if you change the datatypes of @flt and @res in the code
> above to decimal(38,10), the results change to
>
>
> ---------------------------------------
> 21. 00000000000001250000
0000000000
>
>
> ---------------------------------------
> 21. 00000000000001250000
0000000000
>
>
> --
> Hugo Kornelis, SQL Server MVP

Sponsored Links