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.

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.