Solved

Rounding Off Numbers

Posted on 2002-04-22
6
404 Views
Last Modified: 2008-02-07
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
Comment
Question by:Benny00
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 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

by:spcmnspff
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

by:sparcu
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 32

Expert Comment

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

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

Author Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962068
i must admit that the other suggestions where ALL better than mine :-(
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 29
How to share SSIS Package? 6 37
export sql results to csv 6 36
TSQL - How to declare table name 26 30
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 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