round function does not work as expected

[Dick Swager]

I am using the round function in a view in order to display data to a
specified number of decimal places. In general it displays the data as
expected but occasionally there are values that are displayed to many
decimal places. Maybe I should be using some other mechanism to control the
display. Here is code that is similar

declare @x float
declare @y float

set @x = 4.0 * atan (1.0)
set @y = 3.8694336414337158

select
round (@x, 4) as 'pi',
round (@y, 4) as 'offending value'

/* expected result
pi offending value
3.1416 3.8694
*/
/* actual result
pi offending value
3.1416 3.8693999999999997
*/

How should I be doing this to get the desired result?

Now that I think about it, maybe this is a bad practice anyway because if
someone tries to use the value from the view, it will be only an
approximation of the actual value. Is this true?

Thanks, Dick

[Tom Cooper]

Float is a binary datatype and cannot store exactly most decimal values. So
it is impossible to store 3.1416 or 3.8694 exactly in a float. But the
Round() returns the same datatype as the first input parameter. So you are
getting the result as a float. So the float value returned for round(@x,4)
is the closest posible float value to 3.1416 (which is 3.1415999999999999)
and for round(@y,4) the closest possible float value to 3.8694 (which is
3.869399999999997). Then whatever software (like QA) you are using to
display the results decided to show 3.1415999999999999 as 3.1416, but to
leave 3.869399999999997 as it is. One way around this is to cast the
results as a decimal with four positions to the right of the decimal place.
For example,

select
Cast(round (@x, 4) as decimal(5,4)) as 'pi',
Cast(round (@y, 4) as decimal(5,4)) as 'offending value'

Tom

Post by Dick Swager
I am using the round function in a view in order to display data to a
specified number of decimal places. In general it displays the data as
expected but occasionally there are values that are displayed to many
decimal places. Maybe I should be using some other mechanism to control
the display. Here is code that is similar
select
/* expected result
pi offending value
3.1416 3.8694
*/
/* actual result
pi offending value
3.1416 3.8693999999999997
*/
How should I be doing this to get the desired result?
Now that I think about it, maybe this is a bad practice anyway because if
someone tries to use the value from the view, it will be only an
approximation of the actual value. Is this true?
Thanks, Dick

[Eric Isaacs]

I just want to add onto what Tom suggested. If you expect precision,
don't use the FLOAT datatype at all. Declare your @X and @Y variables
as NUMERIC or DECIMAL datatypes with the required decimal length
precision and you should get the results you expect.

declare @x DECIMAL(20, 17)
declare @y DECIMAL(20, 17)

set @x = 4.0 * atan (1.0)
set @y = 3.8694336414337158

PRINT @x
PRINT @y

[Plamen Ratchev]

Here is interesting reading from Hugo Kornelis on calculations with
real/float vs. decimal/numeric:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx

Plamen Ratchev
http://www.SQLStudio.com

[Dick Swager]

Thanks for the info guys. The cast to decimal does what I had intended. It
takes care of the rounding as well so the use of the round function is
redundant.

Am I correct in thinking that I need to be careful to use the table with the
float variable and not that view into the table to get the value for
subsequent calculations? That is, in using the value in the table I get an
approximate (to 16 decimal places) number but if I use the view to get the
same value it will be exact (to 4 decimal places). Very interesting. I had
always assumed that one could query a view in the same way as querying the
table directly and get the same value but it looks like that ain't so.

I'm on my way to do some testing with this stuff to see what actually
happens.

Thanks again,
Dick

Post by Plamen Ratchev
Here is interesting reading from Hugo Kornelis on calculations with
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx
Plamen Ratchev
http://www.SQLStudio.com