Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Rounding Off Numbers

Posted on 2002-04-22
Medium Priority
409 Views
I am new to SQL and I was wondering how I can round a number 35.5555555555555555500 down to 3 decimal places.  I am receiving this number by dividing 2 numbers.  Thanks.
0
Question by:Benny00
[X]
###### 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

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 400 total points
ID: 6960025
these are the steps:
multiply by 1000,
cast to int,
cast to decimal,
divide by 1000

thus:
select (cast ( cast ( 1000 * (col1 /col2)  as int)  as decimal(20,3))/1000

should work fine (sorry for any typos, I typed directly here)

CHeers
0

LVL 5

Expert Comment

ID: 6960059
Benny, There is a round function.  But you should also convert the datatype from Float to Numeric.  Try a convert when you divide, something like this:

Select Cast(Round(1stField/2ndField,3) As Numeric(6,3))

Now for the numeric datatype the numbers in parenthesis mean:

6 - over all length (in digits) of number
3 - number of digits to the right of the decimal place.

so i'll get numbers like:

364.238
3.035
23.340
123.511
0.420

So you may need to adust the 6 to the overall length of the largest possible number you have.

Hope this helps =).
0

Expert Comment

ID: 6960065
declare @d float
set @d = 35.55555555555555555500
select cast(@d as decimal(10,3))

or just
select cast(35.55555555555555555500  as decimal(10,3))

the result is  35.556

3 is the number of digits after the decimal point
10 is total number of digits in the number
0

LVL 32

Expert Comment

ID: 6960333
Personally, I use the innate Round function:

Select Round(@Var1/@Var2, 3) -- Round to 3 Decimal Places
0

Author Comment

ID: 6960836
Thanks for all the help everyone.  Angellll gave me the first one that worked.  Thanks again.
0

LVL 143

Expert Comment

ID: 6962068
i must admit that the other suggestions where ALL better than mine :-(
0

## Featured Post

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll