Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Round 3 decimal places to 2 decimal places.

Posted on 2004-08-23
7
Medium Priority
?
5,578 Views
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.

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.

0
Comment
Question by:Canamshopping
7 Comments
 
LVL 9

Expert Comment

by:SoftEng007
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
0
 
LVL 17

Expert Comment

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

Expert Comment

by:davehilditch
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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 70

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:

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
 
LVL 8

Expert Comment

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

bukko
0
 
LVL 4

Accepted Solution

by:
davehilditch earned 2000 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)

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

Expert Comment

by:BillAn1
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.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

971 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