float fields doing me crazy 
Author Message
 float fields doing me crazy

Hi,

I've just updated my database to the EURO currency (2 decimal places) from
Escudos (0 decimal places).

I started with Round (in VB) but Round(3,565; 2) returns 3,56 and not 3,57,
unless I add a fourth decimal digit >0.

So I tryed FormatNumber(3,565; 2) and it worked fine. (returned 3,57)

The problem is that when I retrieve the fields from my SQL Server 2000
database, some of them were not really the values I was seing. I'll explain:

I was testing two double vars both with 3,56 but the program acted like they
were diferent. So I subtract each variable with theyre own values (in code)
and some of them did not return 0 (zero). How can  3,56-dVar (which is 3,56)
is not zero?????

So I insist and run a query in my SQL Server 2000 to give me all the fields
(I assure you their stored with 2 decimal places) subtracted with their own
round(field ,2) <> 0 and I received a lot of records. How? Why?

Please help I don't know what to do...

Thanks

Carlos Cruz



Sun, 18 Apr 2004 06:33:54 GMT
 float fields doing me crazy

It's a common question.  The answer is that a computer cannot
store any arbitrary number with complete accuracy.  Consider
1/3--there would be an infinite number of decimal places in a
scalar version of this number.  Evaluating 1/3 in the immediate
window gives something like: 1.33333333.  If you subtract
(1/3) - 1.33333333 you'll end up with some small number.  This
is because some extra "guard" digits are kept in the evaluation
of 1 by 3.

Hopefully, the example above is illustrative of how some
rounding must occur.  I couch the description in terms of
decimal numbers, but internally these floats are being kept as
binary number, so the effects are not as obvious to ten-fingered
computers.


Quote:
> Hi,

> I've just updated my database to the EURO currency (2 decimal
places) from
> Escudos (0 decimal places).

> I started with Round (in VB) but Round(3,565; 2) returns 3,56
and not 3,57,
> unless I add a fourth decimal digit >0.

> So I tryed FormatNumber(3,565; 2) and it worked fine.
(returned 3,57)

> The problem is that when I retrieve the fields from my SQL
Server 2000
> database, some of them were not really the values I was seing.
I'll explain:

> I was testing two double vars both with 3,56 but the program
acted like they
> were diferent. So I subtract each variable with theyre own
values (in code)
> and some of them did not return 0 (zero). How can  3,56-dVar
(which is 3,56)
> is not zero?????

> So I insist and run a query in my SQL Server 2000 to give me
all the fields
> (I assure you their stored with 2 decimal places) subtracted
with their own
> round(field ,2) <> 0 and I received a lot of records. How?
Why?

> Please help I don't know what to do...

> Thanks

> Carlos Cruz



Sun, 18 Apr 2004 06:37:54 GMT
 float fields doing me crazy
As an additional comment, VB uses bankers rounding with the round function.
--
Van den Driessche Willy
For a work in progress :
http://users.skynet.be/wvdd2/index.html


Sun, 18 Apr 2004 06:57:26 GMT
 float fields doing me crazy


Quote:
>As an additional comment, VB uses bankers rounding with the round function.

As an additional, additional comment, since you are using
SQL Server, why are you using float anyway? All real
databases have both currency types and arbitrary precision
numeric types for fields. Just use the correct type and
avoid the whole problem. And if you need to convert to
VB intrinsic types, Currency is the correct type to avoid
loss of precision.


Sun, 18 Apr 2004 10:24:27 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. float fields doing me crazy

2. Crazy Enforced FK - Null value crazy???

3. Identity field gone crazy, jump from 137 to 5000000

4. Conversion from Float(38) to Float(126) and ViceVersa

5. float and float(n) datatypes semantics

6. W4GL: problem with float format in float->char conversion

7. Error 3628 on a value of -1.#INF in a float field

8. Inserting NaN values into float fields

9. null values in float fields

10. bcp datatype oddity on float fields

11. Float field not matching

12. Float field error


 
Powered by phpBB® Forum Software