Why does 28.08 show up as 28.080000000000002 in DataGridView

I'm baffled. I have a column in a SQL Server Express database called
"Longitude," which is a float. When I view the table in a DataGridView,
some of the numbers, which only have two decimal places in the database
show up with *15* decimal places and are ever so slightly off (in the
example in the subject line, by about 2E-15).

I'm not doing any operations on this column. It's just running a stored
procedure which performs a pretty basic SELECT on the table. If I run
the stored procedure in Management Studio Express, all numbers show up
fine (just two decimal places).

What's going on here?

Thanks,

-Dan

Daniel,

Float is an approximate datatype. Lookup approximate numeric data in BOL.

-- Bill

"Daniel Manes" <danthman@cox.net> wrote in message
news:1168376034.199558.192380@k58g2000hse.googlegroups.com...
> I'm baffled. I have a column in a SQL Server Express database called
> "Longitude," which is a float. When I view the table in a DataGridView,
> some of the numbers, which only have two decimal places in the database
> show up with *15* decimal places and are ever so slightly off (in the
> example in the subject line, by about 2E-15).
>
> I'm not doing any operations on this column. It's just running a stored
> procedure which performs a pretty basic SELECT on the table. If I run
> the stored procedure in Management Studio Express, all numbers show up
> fine (just two decimal places).
>
> What's going on here?
>
> Thanks,
>
> -Dan
>

FLOAT is an approximate data type. If you want precision, then use an
appropriate DECIMAL instead of FLOAT.


"Daniel Manes" <danthman@cox.net> wrote in message
news:1168376034.199558.192380@k58g2000hse.googlegroups.com...
> I'm baffled. I have a column in a SQL Server Express database called
> "Longitude," which is a float. When I view the table in a DataGridView,
> some of the numbers, which only have two decimal places in the database
> show up with *15* decimal places and are ever so slightly off (in the
> example in the subject line, by about 2E-15).
>
> I'm not doing any operations on this column. It's just running a stored
> procedure which performs a pretty basic SELECT on the table. If I run
> the stored procedure in Management Studio Express, all numbers show up
> fine (just two decimal places).
>
> What's going on here?
>
> Thanks,
>
> -Dan
>

Daniel Manes <danthman@cox.net> wrote:
> I'm baffled. I have a column in a SQL Server Express database called
> "Longitude," which is a float. When I view the table in a DataGridView,
> some of the numbers, which only have two decimal places in the database
> show up with *15* decimal places and are ever so slightly off (in the
> example in the subject line, by about 2E-15).
>
> I'm not doing any operations on this column. It's just running a stored
> procedure which performs a pretty basic SELECT on the table. If I run
> the stored procedure in Management Studio Express, all numbers show up
> fine (just two decimal places).
>
> What's going on here?

See http://www.pobox.com/~skeet/csharp/floatingpoint.html

For reference, the closest .NET double to 28.08 is exactly
28.0799999999999982946974341757595539093017578125

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote:
> FLOAT is an approximate data type. If you want precision, then use an
> appropriate DECIMAL instead of FLOAT.

Decimal is as "approximate" as float, in that neither can represent
every possible rational number exactly. They just have different bases
- decimal will represent numbers like 0.1234 exactly, but will be
inaccurate with 1/3 in the same way that float is.

Both are floating point types - float is a floating *binary* point
type, and decimal is a floating *decimal* point type.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

Jon Skeet [C# MVP] <skeet@pobox.com> wrote:
> Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote:
> > FLOAT is an approximate data type. If you want precision, then use an
> > appropriate DECIMAL instead of FLOAT.
>
> Decimal is as "approximate" as float, in that neither can represent
> every possible rational number exactly. They just have different bases
> - decimal will represent numbers like 0.1234 exactly, but will be
> inaccurate with 1/3 in the same way that float is.
>
> Both are floating point types - float is a floating *binary* point
> type, and decimal is a floating *decimal* point type.

Apologies - some clarification is required here. The above is certainly
true for the C# float/decimal types. After a bit of digging (I don't
have SQL Server help available at the minute) I believe that any
particular column with a defined precision and scale, a DECIMAL column
in SQL Server is effectively "fixed point" (i.e. the value itself
doesn't specify where the decimal point is, the column does).

That doesn't mean it's "precise" in a way that FLOAT isn't, it just
alters the storage (and therefore the range and precision available).

The above probably isn't terribly clear, but the bottom line is that a
floating point number is a very precise number - it has an exact value
- but not every number can be exactly represented as a floating point
number (given the base/storage size etc). That's true for fixed point
numbers as well, and it's not the "fixedness" that makes DECIMAL more
appropriate for business calculations, but the fact that it uses base
10 instead of base 2.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

Take a look to this discussion:

(Why 9.09 == 9.0899999999999999)
http://www.nnseek.com/e/microsoft.p..._9_089999999999
9999_19543198t.html

Regards

--
Cholo Lennon
Bs.As.
ARG


"Daniel Manes" <danthman@cox.net> wrote in message
news:1168376034.199558.192380@k58g2000hse.googlegroups.com...
> I'm baffled. I have a column in a SQL Server Express database called
> "Longitude," which is a float. When I view the table in a DataGridView,
> some of the numbers, which only have two decimal places in the database
> show up with *15* decimal places and are ever so slightly off (in the
> example in the subject line, by about 2E-15).
>
> I'm not doing any operations on this column. It's just running a stored
> procedure which performs a pretty basic SELECT on the table. If I run
> the stored procedure in Management Studio Express, all numbers show up
> fine (just two decimal places).
>
> What's going on here?
>
> Thanks,
>
> -Dan
>

What I meant by "not approximate" is that if you put 28.08 in a
decimal(5,2), you are never going to get 28.08000000000000002


"Jon Skeet [C# MVP]" <skeet@pobox.com> wrote in message
news:MPG.200e1d1681ab555d98d75f@msnews.microsoft.com...
> Jon Skeet [C# MVP] <skeet@pobox.com> wrote:
>> Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote:
>> > FLOAT is an approximate data type. If you want precision, then use an
>> > appropriate DECIMAL instead of FLOAT.
>>
>> Decimal is as "approximate" as float, in that neither can represent
>> every possible rational number exactly. They just have different bases
>> - decimal will represent numbers like 0.1234 exactly, but will be
>> inaccurate with 1/3 in the same way that float is.
>>
>> Both are floating point types - float is a floating *binary* point
>> type, and decimal is a floating *decimal* point type.
>
> Apologies - some clarification is required here. The above is certainly
> true for the C# float/decimal types. After a bit of digging (I don't
> have SQL Server help available at the minute) I believe that any
> particular column with a defined precision and scale, a DECIMAL column
> in SQL Server is effectively "fixed point" (i.e. the value itself
> doesn't specify where the decimal point is, the column does).
>
> That doesn't mean it's "precise" in a way that FLOAT isn't, it just
> alters the storage (and therefore the range and precision available).
>
> The above probably isn't terribly clear, but the bottom line is that a
> floating point number is a very precise number - it has an exact value
> - but not every number can be exactly represented as a floating point
> number (given the base/storage size etc). That's true for fixed point
> numbers as well, and it's not the "fixedness" that makes DECIMAL more
> appropriate for business calculations, but the fact that it uses base
> 10 instead of base 2.
>
> --
> Jon Skeet - <skeet@pobox.com>
> http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
> If replying to the group, please do not mail me too

Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote:
> What I meant by "not approximate" is that if you put 28.08 in a
> decimal(5,2), you are never going to get 28.08000000000000002

True. If you ask SQL server to divide 1 by 3, however, you certainly
*won't* get an exact answer in a DECIMAL though (or FLOAT, admittedly).

The reason I'm bothering to make the distinction is that there's a
widespread myth that decimal types are "exact" in a way that floating
binary point types aren't - it's just down to people having a natural
bias to base 10. If you consider numbers in base 3 (or 7, or 11, etc)
instead, DECIMAL is just as bad as FLOAT.

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

> Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote:
>> What I meant by "not approximate" is that if you put 28.08 in a
>> decimal(5,2), you are never going to get 28.08000000000000002
>
> True. If you ask SQL server to divide 1 by 3, however, you certainly
> *won't* get an exact answer in a DECIMAL though (or FLOAT, admittedly).
>
> The reason I'm bothering to make the distinction is that there's a
> widespread myth that decimal types are "exact" in a way that floating
> binary point types aren't -

I think that most of us recognize the difference between SET @foo = 0.33 and
SET @foo = 1.0/3 ... in the former, we're choosing to limit the "exact"
nature of the result, and if we choose to store it in a DECIMAL as opposed
to a FLOAT, we know we're going to get 0.33 every time...