Solved

Rounding Off Numbers

Posted on 2002-04-22
6
402 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
The duplicate key value is (<NULL>) 14 46
Sql query to Stored Procedure 6 21
Merge Statement 3 9
Minus first query 1 6
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 …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now