Solved

Round 3 decimal places to 2 decimal places.

Posted on 2004-08-23
7
5,281 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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:

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating SQL script using SQL data and SQL script 8 35
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 31
SQL Update trigger 5 16
Return 0 on SQL count 24 28
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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