Floating point precision

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
================================= 2006MJGOOGLENEWSDon'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
>|||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|||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.000000000000046000000000000000
---
21.000000000000014000000000000000
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.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||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, 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.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> 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.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVPsql