Why does 28.08 show up as 28.080000000000002 in DataGridView

[Jon Skeet [C# MVP]]

By the way, here's shorthand for the set of valid values for NUMERIC(38,
20):

The set of valid values for NUMERIC(38, 20) includes all decimal numbers
with 18 or fewer digits before the decimal point and 20 or fewer digits
after the decimal point.

Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as
floats. It would be painstaking because of subnormals, but it's
certainly doable.

 

[Jon Skeet [C# MVP]]

Either way you render the definition useless. The way it's used in SQL
explains the behavior that can be expected.

.... at the cost of making it less clear what's actually going on, IMO.

When you convert 28.02 into an integer you are storing it as 28. Not
an approximation, but exactly 28. The definition describes the
behavior of the data type.

When you convert 28.08 into a float, you are storing it as
28.0799999999999982946974341757595539093017578125. Not an
approximation, but exactly
28.0799999999999982946974341757595539093017578125

Now, that's certainly less intuitive than 28.08 -> 28, but it's still
an exact number.

Exactly - but the definitions are there to describe to a user (like our
OP) who wants to know which data type will give him the same number he
intended to store.

Unfortunately, by doing so it loses sight of the fact that floats store
numbers exactly - they just can't store all decimal numbers exactly.

It's not that it's difficult to explain or understand; it's just not
the intent of the defintion.

I believe that definitions should be *accurate*, and in my view (I'm
afraid no-one's changed my mind, and I don't expect I've changed anyone
else's mind) the definition *isn't* accurate.

Note that the OP asked why he was getting a 28.080000000000002 instead
of 28.08

Using the terminology common to SQL, we can tell him to use one of the
various data types described as "exact" rather than the "approximate"
data type he is using now. Simple, correct answer that solves his
problem immediately.

We could have told him that without giving the impression that the
float type is "approximate" though:

"When a decimal number is converted into a FLOAT, information can be
lost. It's a lossy conversion because not all decimal numbers can be
exactly represented as binary floating point numbers. Use the DECIMAL
type instead to keep the exact decimal value."

Or, we could give him the nitty-gritty details of converting base2
numbers to base10, which while certainly interesting isn't all that
helpful.

Does the above go into nitty gritty? Nope - but it doesn't give a
misleading impression either, IMO.

 

[Mike C#]

As the OP (!), I can tell you that, yes, this whole debate has been
interesting, but the helpful part is knowing which data type to use
when.

For the particular case at hand (trying to display lat/long values back
to the user exactly as originally entered), I decided to change the
data type of my lat/long columns in SQL to DECIMAL(18, 15). Note, more
precision than necessary for this application but just wanted to see
what happens.

The result: When displayed in a .NET DataGridView, every number
displayed had 15 decimal digits. So, a number orginally entered as
"23.5" showed up as "23.500000000000000." Not what I was hoping for.
Using floats, the DataGridView would display only as many digits as
necessary. With decimals, it shows them all, whether they add useful
information or not.

6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.

 

[Aaron Bertrand [SQL Server MVP]]

Let's go back to what the user is trying to do. I'm trying to enter a

latitude, say, 25.5 degrees. I type in 25.4. Later I realize, oops,
that should have been 25.5. I go in to edit the cell.

How many times is the user going to enter 25.4672362763232 and want to edit
that?

A

 

[Mike C#]

Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as
floats. It would be painstaking because of subnormals, but it's
certainly doable.

I can't wait to read it.

 

[Mike C#]

Yes it does - if I have a number which can be accurately represented as
a float, I can perform sufficient conversions to store it exactly as a
float, and retrieve it too.

If you have a number which can be accurately represented as a float, then
you don't need to perform "sufficient conversions" and store/retrieve the
values via BINARY type. All that functionality is built in to the FLOAT
type as it stands. So again I ask, what good does it do you to store and
retrieve floating point decimal values as BINARY types?

BTW, I'm still waiting for you definition of "exact" types...

 

[Jon Skeet [C# MVP]]

I can't wait to read it.

Frankly, I don't have the energy. I don't know whether you genuinely
don't believe it's possible or whether you really want me to waste my
time on it. There are plenty of descriptions of how floats are stored,
and it's far from difficult to reverse engineer that to work out what
*can* be stored.

Why would it possibly be worth my time writing it up?

 

[Daniel Manes]

How many times is the user going to enter 25.4672362763232 and want to edit
that?

If you're trying to say my example was an exaggeration, fine. Sometimes
people do that to make a point. All I'm saying is that trailing zeros
are sometimes a nuisance, and it would be nice if there were an easy
way to get rid of them without losing any data in the process.

-Dan

 

[Aaron Bertrand [SQL Server MVP]]

If you're trying to say my example was an exaggeration, fine. Sometimes

people do that to make a point. All I'm saying is that trailing zeros
are sometimes a nuisance, and it would be nice if there were an easy
way to get rid of them without losing any data in the process.

I'm sure in C# you could work out a way to format the data as a string,
removing trailing zeros but keeping all relevant decimal places.

A

 

[Daniel Manes]

6 digits past the decimal point is accurate to about 4.4 inches in
geocoding. Five digits past the decimal point is about 3.6 feet of
accuracy. Unless you're trying to use a spy satellite to read license
plates, 5 places past the decimal point should be plenty.

Thanks, Mike,

That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.

-Dan

 

[Stephany Young]

Rule of thumb is:

For decimal places of decimal degrees of longitude at the equator:

3rd decimal place is sub-Kilometer

4th decimal place is sub-Hectometer

5th decimal place is sub-Decameter

6th decimal place is sub-meter

7th decimal place is sub-centimeter

8th decimal place is sub-millimeter

 

[Stephany Young]

Sorry! Correction.

Rule of thumb is:

For decimal places of decimal degrees of longitude at the equator:

1st decimal place is sub-Kilometer

2nd decimal place is sub-Hectometer

3rd decimal place is sub-Decameter

4th decimal place is sub-meter

5th decimal place is sub-decimeter

6th decimal place is sub-centimeter

7th decimal place is sub-millimeter

 

[Jon Skeet [C# MVP]]

If you have a number which can be accurately represented as a float, then
you don't need to perform "sufficient conversions" and store/retrieve the
values via BINARY type. All that functionality is built in to the FLOAT
type as it stands. So again I ask, what good does it do you to store and
retrieve floating point decimal values as BINARY types?

Well, the only way I was able to get the *exact* value out of a float
so that I could then convert it (in C#) into a double was to use
binary. I suspect if I'd used C# to retrieve the value in the first
place (rather than just Query Analyzer) I would have got the right
answer that way - it's just a shame that you can't (as far as I can
tell) get SQL Server itself to tell you the exact value of the float.

BTW, I'm still waiting for you definition of "exact" types...

I wouldn't choose to use the words "exact" or "approximate" in the
first place.

 

[Erland Sommarskog]

Either 28.08 shouldn't be regarded as being "in the range of valid
values" for FLOAT, *or* it should be regarded as being "in the range of
valid values" for DECIMAL(1,2). I'm happy to use either definition, so
long as it's used consistently.

The entire point you seem to be missing in this thread is what we use
types for. No on would use an int to store 28.02, but we may want to
use float to that aim. But it is not possible to store 28.02 exactly in
a float, so we get an approximation of the value we are really thinking of.

That is why float/real are approxamite, and decimal and int are not. They
are approximations of what we really want to store.

Yes, it is true that it is possible to store some values with very many
decimals exactly in a float, but that is completely irrelevant, because
floats are very rarely if ever used for that aim.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Jon Skeet [C# MVP]]

The entire point you seem to be missing in this thread is what we use
types for. No on would use an int to store 28.02, but we may want to
use float to that aim. But it is not possible to store 28.02 exactly in
a float, so we get an approximation of the value we are really thinking of.

I've never disputed that. I've never disputed the use of decimal as a
way of storing decimal numbers exactly. I've said several times that I
absolutely agree with the use of decimal for the OP's problem. I'm not
concerned about the *use* of the type - we can all agree on that. How
you use something doesn't, to my mind, define what it *is*. Most of the
32-bit integers I use in .NET will never actually have numbers above
1000 in them, but that doesn't mean that the type itself can't store
anything above 1000 - what it can and can't store is precisely defined
regardless of how I choose to use it.

My concern is that float is being arbitrarily labeled as "approximate"
just because it can't store all values of one particular base, despite
the fact that decimal can't store all values of other bases. Yes, base
10 is obviously the most widely used base, but it *is* just a base.
We're lucky that it's a multiple of 2 - otherwise exact float values
wouldn't be exactly representable in decimal...

Any float value is exact in and of itself. If it's an approximation to
some other value it was originally converted from, so be it - that
doesn't, to my mind, make the type itself "approximate".

That is why float/real are approxamite, and decimal and int are not. They
are approximations of what we really want to store.

The value in the float is only approximate equal to the decimal value
which was originally converted, but in itself it is an exact number.
Given a floating point value, there is a well-defined, precise number
that value represents.

Yes, it is true that it is possible to store some values with very many
decimals exactly in a float, but that is completely irrelevant, because
floats are very rarely if ever used for that aim.

Frankly, how many *decimals* a float stores is irrelevant to me. What
I'm interested in is whether the value stored in a float can be
regarded as "exact" or merely "approximate". Given a float
representing, say, 1.0101, that is an exact value.

 

[Erland Sommarskog]

My concern is that float is being arbitrarily labeled as "approximate"

No, it is not arbitrary. It relates to what humans would like to be
able to store, but which is not always possible. The fact that the floating
number actually belongs to the set of decimal numbers is completely
rrelevant when it comes to usage.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mike C#]

That's exactly what I was just trying to calculate. I was figuring
accurate to a yard would be plenty and was just going to base my
decimal digits on that. I did my calculations a little differently,
though, since users can enter in either degrees or degs-mins-secs. I
somehow came up with ~two feet per hundredth of a second (centisecond?)
of arc. Which is equivalent to 0.0000028 degrees. Either way, looks
like decimal(9, 6) ought to be enough, even for detecting relatively
small movements.

There's a blog entry over at geocoder.us where he breaks down the digits of
precision in a table format:
http://geocoder.us/blog/2006/03/23/how-many-digits-are-enough/. His table
has decimals, degrees, statute miles, feet and inches. For East-West
distances it's different (since the distance between lines of longitude gets
shorter as you move away from the equator), but it's pretty close to the
same thing over the continental U.S. If you're geocoding much farther north
or far south of the equator, you will probably need to make adjustments. At
any rate, 6 digits past the decimal point should be adequate regardless of
where you're geocoding.

 

[Mike C#]

Well, the only way I was able to get the *exact* value out of a float
so that I could then convert it (in C#) into a double was to use
binary. I suspect if I'd used C# to retrieve the value in the first
place (rather than just Query Analyzer) I would have got the right
answer that way - it's just a shame that you can't (as far as I can
tell) get SQL Server itself to tell you the exact value of the float.

Yet even with C# you can't always retrieve the *exact* value you originally
assigned. You retrieve an approximation of that value... You put 28.08
into the bucket and pull out 28.079999923706055, which is not the "right
answer".

I wouldn't choose to use the words "exact" or "approximate" in the
first place.

<Message Author="Jon Skeet [C# MVP]">
"No, by my definition pretty much all datatypes are exact."
</Message>

I'm still waiting to hear your definition, but only because you indicated
that you actually have one.

 

[Shuurai]

... at the cost of making it less clear what's actually going on, IMO.

While making it more clear what can be expected as a result. Those of
us who know how it's working don't need the definition as much as
someone who's just looking to get the same number out that they put in.

When you convert 28.08 into a float, you are storing it as
28.0799999999999982946974341757595539093017578125. Not an
approximation, but exactly
28.0799999999999982946974341757595539093017578125

And you'll note that 28.0 !=
28.0799999999999982946974341757595539093017578125

Now, that's certainly less intuitive than 28.08 -> 28, but it's still
an exact number.

Yes, it's an exact number. It's just not the exact number that we
stored.

Unfortunately, by doing so it loses sight of the fact that floats store
numbers exactly - they just can't store all decimal numbers exactly.

So your argument is that they store SOME numbers exactly?

I believe that definitions should be *accurate*, and in my view (I'm
afraid no-one's changed my mind, and I don't expect I've changed anyone
else's mind) the definition *isn't* accurate.

Definitions need to have a balance between usefulness and accuracy.
Your definition, even if technically accurate (which I don't believe it
is) just isn't useful.

We could have told him that without giving the impression that the
float type is "approximate" though:

"When a decimal number is converted into a FLOAT, information can be
lost. It's a lossy conversion because not all decimal numbers can be
exactly represented as binary floating point numbers. Use the DECIMAL
type instead to keep the exact decimal value."

Which is absolutely correct. The definition is used to describe what
can be expected of the data type. It doesn't address (or need to
address) anything that is going on in the background. The point of the
definition is to tell the user which types will store 28.08 as 28.08.

Does the above go into nitty gritty? Nope - but it doesn't give a
misleading impression either, IMO.

Not misleading; just not particularly useful.

 

[Mike C#]

Frankly, I don't have the energy. I don't know whether you genuinely
don't believe it's possible or whether you really want me to waste my
time on it. There are plenty of descriptions of how floats are stored,
and it's far from difficult to reverse engineer that to work out what
*can* be stored.

Why would it possibly be worth my time writing it up?

<Message Author = "Jon Skeet [C# MVP]">
"Well, in that case I could (somewhat painstakingly) write a similar
description in terms of binary strings which are representable as floats."
</Message>
....
<Message Author = "Jon Skeet [C# MVP]">
"No, by my definition pretty much all datatypes are exact. "
</Message>
....
<Message Author = "Jon Skeet [C# MVP]">
"I'll do that (publish a complete list of valid values for the FLOAT and
REAL types) if you publish a complete list of valid values for
NUMERIC(38,20)."
</Message>

I'm only interested in your definitions and descriptions because you
indicate that you either have them on hand, or are able to produce them. If
you don't have any definitions and/or can't produce what you said you can,
then I'd say the whole issue is fairly well resolved.