# Round 3 decimal places to 2 decimal places.

Here is problem:

I was provided prices to 2 and 3 decimal places; we need to be only 2 decimal places.

Set-up:
Table called product
column called price - data type float - length 8 characters

e.g. of data
.224
8.671
2.315
.191
.73
.20

1. Is there a SQL query I could run on the price column to convert to 2 decimal places but round everything up? e.g. 0.224 would be 0.23
2. If not, is there a simple SQL query I could run on the price column to round all rows to 2 decimal places?
3. Is there a way of doing this without converting/changing the data type from float to numeric?

Thanks for the help in advance.

###### Who is Participating?

x

Commented:
Sorry bukko, but BillAn1s answer is incorrect in the context of the question.

First off, the question required that everything be rounded up.  By using BillAn1s answer, the following:

select round(0.224, 2)

will return 0.220 and not 0.23 - not only does the answer have 3 decimal places, but it also is completely incorrect as he specifically asked to round everything up.  The only correct way to do this in SQL server is to add half of the last decimal point you need the accuracy to and *then* perform the rounding.

Secondly, since the column is a float, if the column had the value 0.224 contained within it, the result of BillAn1s answer would produce a float number with innacuracies, as can be seen by running the following in Query Analyzer:

(it actually works fine for 0.224 so I've picked another number to demonstrate this second point)

select cast(round(0.225, 2) as float)

0.23000000000000001

Which clearly is undesirable as it is neither accurate or to two decimal places.

Even if you perform the following:

select round(cast(round(0.225,2) as float), 2)

It still returns:

0.23000000000000001

The correct method is as stated and he will have to choose a different column format or use a View to make a computed column of the underlying float column - in the computed column he can not only convert to decimal, but also at the same time add half the last decimal - 0.005 in this case - to produce the desired result.

Dave Hilditch.
0

Commented:
select 1.234  price_1,convert(decimal(5,2), 1.234)  price_2,
convert(decimal(5,2), 1.234 + .002)  price_3

price_2 will convert and round 5 down 6 up

price 3 will convert and round 4 up
0

Commented:
you can use the round function
e.g. round(0.225,2) gives 0.23 etc
0

Commented:
You can do the following:

select cast(0.224 + 0.005 as decimal(10,2))

By adding half of the third decimal place to the value you ensure it always rounds up.

If you try the same as above in query analyzer with a float you'll find you get rounding errors - I would avoid using a float for monetary values if I was you.

Dave Hilditch
0

Senior DBACommented:
Personally I too would use DECIMAL rather than FLOAT because of its inaccuracy; FLOAT is only needed for *very* large numbers.

And I would use CEILING to avoid any roundup/rounddown issues:

DECLARE @price FLOAT
SET @price = 8.671
SELECT CEILING(CAST(@price AS DECIMAL(10,3)) * 100.00) / 100.00

SELECT CEILING(CAST(price AS DECIMAL(10,3)) * 100.00) / 100.00
FROM ...
0

Commented:
BillAn1's answer is the best. Use the built-in ROUND function.

bukko
0

Commented:
Yes, I agree, that ROUND is not the correct method, as the question wants to round up, rather than regular rounding.
and, yes to avoid further errors propagating, it is necessary to convert to a precise data type, rather than an approximate datatype.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.