473,883 Members | 2,087 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Why does 28.08 show up as 28.080000000000 002 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

Jan 9 '07
130 6668
Aaron Bertrand [SQL Server MVP] <te*****@dnartr eb.noraawrote:
Aaron Bertrand [SQL Server MVP] <te*****@dnartr eb.noraawrote:
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.080000000000 00002
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...
But if you were to express floating binary point numbers in a binary
format (eg 0.01101) then you'd get the exact same result back when you
reformatted it as binary. The nature of the literal formats available
doesn't alter whether or not a data type is essentially approximate or
not.

The "approximat e" nature of binary floating point isn't because it's
floating point, and isn't changed by using a decimal format. It's just
the inability to exactly represent all *decimal* numbers, which isn't
the same thing as all numbers.

I personally believe that's *not* well understood - hence people (e.g.
3 different people in this thread) referring to floating binary point
numbers as "approximat e" when they're not at all - they're exact
numbers which may only be approximately equal to the number you
originally tried to set them to.

As an example of what I mean, consider integer types. Are they
"approximat e"? No - they represent exact numbers. However, if you do
(in C#):

int x = (int) 10.3;

The value of x won't be 10.3, it will be 10. 10 is an exact number, but
the conversion from 10.3 was an approximation. The same is true if you
have a float and do

SET @my_float = 0.33 (SQL)
or
float myFloat = 0.33f; (C#)

my_float represents an exact number, but the conversion from the
literal "0.33" to floating binary point is an approximating one. The
differences between the floating binary point conversion and the
conversion to an integer earlier are:

1) The integer conversion is explicit, highlighting that data may be
lost
2) When reformatted as a decimal value, an integer is always "tidy"
whereas a float often isn't (as evidenced by the subject line of this
thread)

I don't see either of those as reasons to describe floating binary
point numbers as "approximat e" when integers aren't described in that
way.

Given a floating point value, I can tell you *exactly* what that number
is as a decimal expansion. In what sense is the value (or the type)
"approximat e"? It's important (IMO) not to be blinded by the bias of
the fact that humans tend to represent numbers as decimals.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 10 '07 #11

"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** @msnews.microso ft.com...
Aaron Bertrand [SQL Server MVP] <te*****@dnartr eb.noraawrote:
>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.080000000000 00002

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).
That's a shortcoming of numbers in general, not of SQL Server's
representation of numeric values. T-SQL defines real and float as
"approximat e" data types because using the IEEE standard binary
representation not all numbers in the range of valid values can be stored in
an exact representation. Or, as Aaron pointed out, if you put in "28.08"
you can get back "28.08000000000 000002".

http://msdn2.microsoft.com/en-us/library/ms173773.aspx

Decimal and numeric are "exact" data types because they can represent all
the numbers in their range of valid values in an exact representation.
I.e., "28.08" is stored exactly as 28.08.
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.
As pointed out, decimal types are "exact" in that they can "exactly"
represent every single number in their range of valid values. IEEE standard
floating point binary types are "approximat e" because they cannot.
Jan 10 '07 #12
Mike C# <xy*@xyz.comwro te:
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).

That's a shortcoming of numbers in general, not of SQL Server's
representation of numeric values.
No, it's a shortcoming of storing numbers as sequences of digits,
whatever the base. You could represent all rationals as one integer
divided by another integer, and always have the exact value of the
rational (assuming arbitrary length integers) - until you started to
perform operations like square roots, of course.
T-SQL defines real and float as
"approximat e" data types because using the IEEE standard binary
representation not all numbers in the range of valid values can be stored in
an exact representation.
The same is true for decimal, however: not all numbers in the range of
valid values can be stored in an exact representation. All *decimal*
values can
Or, as Aaron pointed out, if you put in "28.08"
you can get back "28.08000000000 000002".
Just as if you had a way of representing base 3 literals and you put in
".1" for a decimal you wouldn't (necessarily, at least) get ".1" back
when you reformatted to base 3 - because information would have been
lost.
http://msdn2.microsoft.com/en-us/library/ms173773.aspx
I disagree with MSDN as well. It's not the first time, including for
floating point types - I had to battle with people at MSDN for them to
correct the description of the .NET Decimal type, which was originally
documented as a fixed point type, despite it clearly not being so.
Fortunately that's now fixed...
Decimal and numeric are "exact" data types because they can represent all
the numbers in their range of valid values in an exact representation.
I.e., "28.08" is stored exactly as 28.08.
No, they can't, any more than binary floating point types can. They can
only store numbers to a certain *decimal* precision, in the same way
that floating binary point types can only store numbers to a certain
*binary* precision.

As an example, the real (and even rational) number obtained by dividing
1 by 3 is within the range of decimal (as expressed in the MSDN page
for decimal: "valid values are from - 10^38 +1 through 10^38 - 1"), but
*cannot* stored exactly.
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.

As pointed out, decimal types are "exact" in that they can "exactly"
represent every single number in their range of valid values. IEEE standard
floating point binary types are "approximat e" because they cannot.
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.

1/3 is just as "real" a number as 1/10 - and yet you ignore the fact
that it can't be exactly represented by a decimal, even though it's in
the range. Why should the failure to represent 1/10 accurately make a
data type be regarded as "approximat e" when the failure to represent
1/3 doesn't? From a mathematical point of view, there's very little
difference.

From a *business* point of view, there's a large difference in terms of
how useful the types are and for what operations, but that doesn't
affect whether or not a data type should be regarded as fundamentally
"exact" or not, IMO.
Look at it from the raw data point of view: both types essentially
represent a long sequence of digits and a point somewhere within the
sequence. You could choose *any* base - it so happens that the common
ones used are 2 and 10.

Now, from that definition, and disregarding the fact that humans have
10 fingers, what's the difference between a sequence of bits with a
point in and a sequence of decimal digits with a point in that makes
you say that the first is approximate but the second is exact?

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 10 '07 #13

"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
The "approximat e" nature of binary floating point isn't because it's
floating point, and isn't changed by using a decimal format. It's just
the inability to exactly represent all *decimal* numbers, which isn't
the same thing as all numbers.
That seems like hair-splitting, for real.
I personally believe that's *not* well understood - hence people (e.g.
3 different people in this thread) referring to floating binary point
numbers as "approximat e" when they're not at all - they're exact
numbers which may only be approximately equal to the number you
originally tried to set them to.
People in this newsgroup refer to binary floating point numbers as
"approximat e" because that's what the ANSI SQL standard calls them.

"The data types NUMERIC, DECIMAL, INTEGER, and SMALLINT are collectively
referred to as exact numeric types. The data types FLOAT, REAL, and DOUBLE
PRECISION are collectively referred to as approximate numeric types. Exact
numeric types and approximate numeric types are collectively referred to as
numeric types. Values of numeric type are referred to as numbers." -- ANSI
SQL-92
Jan 10 '07 #14

"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
No, it's a shortcoming of storing numbers as sequences of digits,
whatever the base. You could represent all rationals as one integer
divided by another integer, and always have the exact value of the
rational (assuming arbitrary length integers) - until you started to
perform operations like square roots, of course.
And what about irrational numbers?
The same is true for decimal, however: not all numbers in the range of
valid values can be stored in an exact representation. All *decimal*
values can
The SQL Decimal and Numeric data types are designed to store every single
last number in the range of valid values in an *EXACT* representation.
>Or, as Aaron pointed out, if you put in "28.08"
you can get back "28.08000000000 000002".

Just as if you had a way of representing base 3 literals and you put in
".1" for a decimal you wouldn't (necessarily, at least) get ".1" back
when you reformatted to base 3 - because information would have been
lost.
Are we talking about information loss from the simple act of converting from
one base to another, which is what the Decimal and Numeric types address; or
are you talking about information loss from performing operations on data
which goes back to your 1/3 example earlier?
>http://msdn2.microsoft.com/en-us/library/ms173773.aspx

I disagree with MSDN as well. It's not the first time, including for
floating point types - I had to battle with people at MSDN for them to
correct the description of the .NET Decimal type, which was originally
documented as a fixed point type, despite it clearly not being so.
Fortunately that's now fixed...
You need to present your arguments about this to ANSI, since these are the
definitions they put forth.
>Decimal and numeric are "exact" data types because they can represent all
the numbers in their range of valid values in an exact representation.
I.e., "28.08" is stored exactly as 28.08.

No, they can't, any more than binary floating point types can. They can
only store numbers to a certain *decimal* precision, in the same way
that floating binary point types can only store numbers to a certain
*binary* precision.
They can store *every single valid value in their defined set of values* in
an exact representation. With Numeric and Decimal types precision and scale
that you specify define the valid set of values.
As an example, the real (and even rational) number obtained by dividing
1 by 3 is within the range of decimal (as expressed in the MSDN page
for decimal: "valid values are from - 10^38 +1 through 10^38 - 1"), but
*cannot* stored exactly.
You might want to re-read that, because you seem to have picked out just
enough of a single sentence to support your argument, but completely ignored
the part that doesn't. Here's the full sentence for you (the emphasis is
mine):

"WHEN MAXIMUM PRECISION IS USED, valid values are from - 10^38 +1 through
10^38 - 1."

That also defines a default scale of "0". If, however, you define a
different precision and/or scale, the valid set of values changes.
>As pointed out, decimal types are "exact" in that they can "exactly"
represent every single number in their range of valid values. IEEE
standard
floating point binary types are "approximat e" because they cannot.

You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.
Of course I'm biased to decimal numbers, as are most of the people here who
were born with 10 fingers and 10 toes. Now let's talk about your bias
against irrational numbers, since you seem to be tip-toeing all around them
here.
1/3 is just as "real" a number as 1/10 - and yet you ignore the fact
that it can't be exactly represented by a decimal, even though it's in
the range. Why should the failure to represent 1/10 accurately make a
data type be regarded as "approximat e" when the failure to represent
1/3 doesn't? From a mathematical point of view, there's very little
difference.
There is no "fraction" data type in SQL, which you might consider a
shortcoming of the language. There is also no "fraction" data type built
into C#, C++, or most other languages. The reason for that might be a huge
bias or conspiracy against vulgar fractions, or it might be the lack of a
useful vinculum in the original ASCII character set, or who it might just be
that most people just don't find it all that useful.

Regardless of the reason, 1/10 is not a repeating decimal in decimal (yes,
I'm showing my "bias" for decimal again). It has a finite representation in
decimal. 1/3 is a repeating decimal in decimal. Since it's decimal
representation repeats infinitely, it's impossible to store it exactly
anywhere. However, with regards to Decimal and Numeric data types in SQL,
"0.33333" is not a member of the set of valid values for a DECIMAL (10, 2)
or a NUMERIC (10, 3) [for instance].
From a *business* point of view, there's a large difference in terms of
how useful the types are and for what operations, but that doesn't
affect whether or not a data type should be regarded as fundamentally
"exact" or not, IMO.
The definitions of SQL DECIMAL and NUMERIC specifically allow you to define
the precision and scale. By doing so you define the number of total digits
and the number of digits after the decimal point. Any numbers that are in
the set of valid values for the precision and scale that you define will be
stored *exactly*. Any numbers that are not in the set of valid values for
that precision and scale will not be stored exactly. SQL REAL and FLOAT
types do not come with this guarantee.
Look at it from the raw data point of view: both types essentially
represent a long sequence of digits and a point somewhere within the
sequence. You could choose *any* base - it so happens that the common
ones used are 2 and 10.
And there happen to be very good reasons for that...
Now, from that definition, and disregarding the fact that humans have
10 fingers, what's the difference between a sequence of bits with a
point in and a sequence of decimal digits with a point in that makes
you say that the first is approximate but the second is exact?
Quite simply because with NUMERIC and DECIMAL types I can define the
precision and scale, and every valid value in that range will be stored
*EXACTLY*. REAL and FLOAT come with pre-defined ranges, but have no
guarantees concerning how the exactness with which your numeric data will be
stored. As we saw, even a number as simple as 28.08 is stored as an
approximation of 28.08 (or 2808/100 if you prefer your fractions). Whereas
with a NUMERIC (10, 2) it would be stored as -- well, "28.08" *EXACTLY*.
Jan 10 '07 #15
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.
Well, yeah, Sherlock.

The OP passed 28.08 into the database. When he asked for the value back, he
got a different answer. He asked how to fix it, so I guess, yeah, our bias
in this case is toward using DECIMAL. Where, regardless of what fantasy
world you're converting to base 3 et. al., when you input 28.08 and then ask
for the value back, you're still going to get 28.08. This isn't about math
theory or MSDN or base 3 or 1/3 or some religious war about whether you
think the standards' definition of "approximat e" is acceptable to you. It's
about storing a known value (not an expression, like 1/3) with a fixed
number of decimal places, and getting the same number back, every time, in a
deterministic fashion.

A
Jan 10 '07 #16
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.
Well, yeah, Sherlock.

The OP passed 28.08 into the database. When he asked for the value back, he
got a different answer. He asked how to fix it, so I guess, yeah, our bias
in this case is toward using DECIMAL. Where, regardless of what fantasy
world you're converting to base 3 et. al., when you input 28.08 and then ask
for the value back, you're still going to get 28.08. This isn't about math
theory or MSDN or base 3 or 1/3 or some religious war about whether you
think the standards' definition of "approximat e" is acceptable to you. It's
about storing a known value (not an expression, like 1/3) with a fixed
number of decimal places, and getting the same number back, every time, in a
deterministic fashion.

A
Jan 10 '07 #17
Mike C# <xy*@xyz.comwro te:
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
The "approximat e" nature of binary floating point isn't because it's
floating point, and isn't changed by using a decimal format. It's just
the inability to exactly represent all *decimal* numbers, which isn't
the same thing as all numbers.

That seems like hair-splitting, for real.
I see nothing hair-splitting about recognising that 1/3 is a number.
I personally believe that's *not* well understood - hence people (e.g.
3 different people in this thread) referring to floating binary point
numbers as "approximat e" when they're not at all - they're exact
numbers which may only be approximately equal to the number you
originally tried to set them to.

People in this newsgroup refer to binary floating point numbers as
"approximat e" because that's what the ANSI SQL standard calls them.
You may notice that there are multiple newsgroups involved here...
"The data types NUMERIC, DECIMAL, INTEGER, and SMALLINT are collectively
referred to as exact numeric types. The data types FLOAT, REAL, and DOUBLE
PRECISION are collectively referred to as approximate numeric types. Exact
numeric types and approximate numeric types are collectively referred to as
numeric types. Values of numeric type are referred to as numbers." -- ANSI
SQL-92
Just because ANSI says something doesn't mean it's right, IMO.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 10 '07 #18
Mike C# <xy*@xyz.comwro te:
"Jon Skeet [C# MVP]" <sk***@pobox.co mwrote in message
news:MP******** *************** *@msnews.micros oft.com...
No, it's a shortcoming of storing numbers as sequences of digits,
whatever the base. You could represent all rationals as one integer
divided by another integer, and always have the exact value of the
rational (assuming arbitrary length integers) - until you started to
perform operations like square roots, of course.

And what about irrational numbers?
That's why I said you couldn't cope when square roots are involved.

Basically we don't *have* a good way of exactly representing numbers,
unless you want to keep everything at a symbolic level.
The same is true for decimal, however: not all numbers in the range of
valid values can be stored in an exact representation. All *decimal*
values can

The SQL Decimal and Numeric data types are designed to store every single
last number in the range of valid values in an *EXACT* representation.
1/3 is in the range, but can't be stored exactly. Which part of that
statement do you disagree with?
Or, as Aaron pointed out, if you put in "28.08"
you can get back "28.08000000000 000002".
Just as if you had a way of representing base 3 literals and you put in
".1" for a decimal you wouldn't (necessarily, at least) get ".1" back
when you reformatted to base 3 - because information would have been
lost.

Are we talking about information loss from the simple act of converting from
one base to another, which is what the Decimal and Numeric types address; or
are you talking about information loss from performing operations on data
which goes back to your 1/3 example earlier?
They're the same thing. Converting 0.1 base 3 (i.e. 1/3) into a decimal
*is* a conversion from one base to another, and the Decimal and Numeric
types *do not* store that number exactly.
http://msdn2.microsoft.com/en-us/library/ms173773.aspx
I disagree with MSDN as well. It's not the first time, including for
floating point types - I had to battle with people at MSDN for them to
correct the description of the .NET Decimal type, which was originally
documented as a fixed point type, despite it clearly not being so.
Fortunately that's now fixed...

You need to present your arguments about this to ANSI, since these are the
definitions they put forth.
I'm happy to disagree with any number of people, if what they're saying
doesn't make sense.
Decimal and numeric are "exact" data types because they can represent all
the numbers in their range of valid values in an exact representation.
I.e., "28.08" is stored exactly as 28.08.
No, they can't, any more than binary floating point types can. They can
only store numbers to a certain *decimal* precision, in the same way
that floating binary point types can only store numbers to a certain
*binary* precision.

They can store *every single valid value in their defined set of values* in
an exact representation. With Numeric and Decimal types precision and scale
that you specify define the valid set of values.
In that case the same can be said for Float. Every Float value is an
exact value - you can write down the *exact* binary string it
represents, and even convert it into an exact decimal representation.
As an example, the real (and even rational) number obtained by dividing
1 by 3 is within the range of decimal (as expressed in the MSDN page
for decimal: "valid values are from - 10^38 +1 through 10^38 - 1"), but
*cannot* stored exactly.

You might want to re-read that, because you seem to have picked out just
enough of a single sentence to support your argument, but completely ignored
the part that doesn't. Here's the full sentence for you (the emphasis is
mine):

"WHEN MAXIMUM PRECISION IS USED, valid values are from - 10^38 +1 through
10^38 - 1."

That also defines a default scale of "0". If, however, you define a
different precision and/or scale, the valid set of values changes.
I don't think that makes any difference - when maximum precision is
used, the range of valid values is given in terms of a minimum and a
maximum, and not every value in that range is exactly representable. If
you're going to treat it as "every value that can be exactly
represented can be exactly represented" then as I say, the same is true
for Float.
As pointed out, decimal types are "exact" in that they can "exactly"
represent every single number in their range of valid values. IEEE
standard
floating point binary types are "approximat e" because they cannot.
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.

Of course I'm biased to decimal numbers, as are most of the people here who
were born with 10 fingers and 10 toes. Now let's talk about your bias
against irrational numbers, since you seem to be tip-toeing all around them
here.
No, I'm recognising the mathematical reality that the existence (and
value) of a number doesn't depend on what base you represent it in.
1/3 is just as "real" a number as 1/10 - and yet you ignore the fact
that it can't be exactly represented by a decimal, even though it's in
the range. Why should the failure to represent 1/10 accurately make a
data type be regarded as "approximat e" when the failure to represent
1/3 doesn't? From a mathematical point of view, there's very little
difference.

There is no "fraction" data type in SQL, which you might consider a
shortcoming of the language. There is also no "fraction" data type built
into C#, C++, or most other languages. The reason for that might be a huge
bias or conspiracy against vulgar fractions, or it might be the lack of a
useful vinculum in the original ASCII character set, or who it might just be
that most people just don't find it all that useful.
I wasn't talking about usefulness, I was talking about existence.
Suggesting that 1/3 isn't a number is silly, IMO.
Regardless of the reason, 1/10 is not a repeating decimal in decimal (yes,
I'm showing my "bias" for decimal again). It has a finite representation in
decimal. 1/3 is a repeating decimal in decimal. Since it's decimal
representation repeats infinitely, it's impossible to store it exactly
anywhere. However, with regards to Decimal and Numeric data types in SQL,
"0.33333" is not a member of the set of valid values for a DECIMAL (10, 2)
or a NUMERIC (10, 3) [for instance].
Indeed.
From a *business* point of view, there's a large difference in terms of
how useful the types are and for what operations, but that doesn't
affect whether or not a data type should be regarded as fundamentally
"exact" or not, IMO.

The definitions of SQL DECIMAL and NUMERIC specifically allow you to define
the precision and scale. By doing so you define the number of total digits
and the number of digits after the decimal point. Any numbers that are in
the set of valid values for the precision and scale that you define will be
stored *exactly*. Any numbers that are not in the set of valid values for
that precision and scale will not be stored exactly. SQL REAL and FLOAT
types do not come with this guarantee.
There's no reason why they shouldn't - why would it not be able to
guarantees tha the number is essentially reproducible, which is all I
can understand that we're talking about? The binary string "0.010101"
represents an exact number and no others.
Look at it from the raw data point of view: both types essentially
represent a long sequence of digits and a point somewhere within the
sequence. You could choose *any* base - it so happens that the common
ones used are 2 and 10.

And there happen to be very good reasons for that...
Of course, which I've never denied.
Now, from that definition, and disregarding the fact that humans have
10 fingers, what's the difference between a sequence of bits with a
point in and a sequence of decimal digits with a point in that makes
you say that the first is approximate but the second is exact?

Quite simply because with NUMERIC and DECIMAL types I can define the
precision and scale, and every valid value in that range will be stored
*EXACTLY*. REAL and FLOAT come with pre-defined ranges, but have no
guarantees concerning how the exactness with which your numeric data will be
stored. As we saw, even a number as simple as 28.08 is stored as an
approximation of 28.08 (or 2808/100 if you prefer your fractions). Whereas
with a NUMERIC (10, 2) it would be stored as -- well, "28.08" *EXACTLY*.
Do you regard 28.08 as in the "range" for FLOAT? It seems unfair to
demand that it is while saying that 1/3 isn't in the "RANGE" for
NUMERIC, but that's what you've basically got to do in order to claim
that FLOAT is "approximat e" but DECIMAL is "exact".

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 10 '07 #19
Aaron Bertrand [SQL Server MVP] <te*****@dnartr eb.noraawrote:
You're showing a bias to decimal numbers. There are perfectly valid
reasons to have that bias and to have that decimal type, but you should
acknowledge that the bias is there.

Well, yeah, Sherlock.
It seems to be a fact which is blinding everyone to the existence of
1/3 as a number.
The OP passed 28.08 into the database. When he asked for the value back, he
got a different answer. He asked how to fix it, so I guess, yeah, our bias
in this case is toward using DECIMAL.
And I never said that wasn't a good idea.
Where, regardless of what fantasy world you're converting to base 3
et. al., when you input 28.08 and then ask for the value back, you're
still going to get 28.08.
No, if you input a number in base 3, convert it to base 10, and then
later convert it back to base 3, you will *not* (always) get the same
number back, for exactly the same reason that 28.08 doesn't come back
as 28.08. Information is lost.
This isn't about math theory or MSDN or base 3 or 1/3 or some
religious war about whether you think the standards' definition of
"approximat e" is acceptable to you.
That's precisely what this part of the thread is about. I never
quibbled with the solution to the OP's problem, merely the terminology
used.
It's about storing a known value (not an expression, like 1/3)
1/3 is a known value - just think of it as the literal "0.1" in base 3.
with a fixed number of decimal places, and getting the same number
back, every time, in a deterministic fashion.
You get determinism with floating binary point too - just not lossless
conversion from base 10, that's all.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 10 '07 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

79
5514
by: Daniel Manes | last post by:
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...
0
9948
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9798
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10864
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7980
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6008
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.