round function does not work as expected

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

"Dick Swager" wrote in message

>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
 >> Stay informed about: round function does not work as expected 

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-ex...-numeri

Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: round function does not work as expected 

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


"Plamen Ratchev" wrote in message

> 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-ex...-numeri
>
> Plamen Ratchev
> http://www.SQLStudio.com
 >> Stay informed about: round function does not work as expected