Round funtion on entire columns in MSSQL?

[steve]

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve

[Hugo Kornelis]

On 5 May 2004 02:40:15 -0700, steve wrote:

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Simon Hayes]

st******************@exact.be (steve) wrote in message news:<79**************************@posting.google. com>...

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon

[Erland Sommarskog]

steve (st******************@exact.be) writes:

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp