Round 3 decimal places to 2 decimal places.

Posted on 2004-08-23
Last Modified: 2008-01-09
Here is problem:

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

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

e.g. of data

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.

Question by:Canamshopping
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 11873862
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
LVL 17

Expert Comment

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

Expert Comment

ID: 11873876
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 11875305
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:

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

Expert Comment

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


Accepted Solution

davehilditch earned 500 total points
ID: 11875715
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)


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:


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.
LVL 17

Expert Comment

ID: 11876025
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.


Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question