Rounding a real number

Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||The ROUND() function returns a number in the data type of the input expressi
on. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an
exact data type where FLOAT is inexact. Since your input was implicitly FLO
AT, the function returned a FLOAT; however, notice that the results did ROUN
D to two significant digits. The trailing digits were due to the nature of
FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would ha
ve gained what you were searching for. The question is whether or not you a
lso needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.35
88@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the poin
t
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
--|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is
a FLOATING DECIMAL POINT data type. This means you store a mantissa and an
exponent to a significant number of digits. This does not mean you don't c
arry around additional digits, just that for your precision and scale, you w
ill have the correct result, but anything beyond these bounds and you will e
nd up with extra stuff.
You will get the same behavior in your programming API if you store a value
in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUB
LE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead o
f FLOAT.
So you know, this problem has been around far longer than SQL Server, or any
RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.33
64@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>