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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Rounding Off Numbers

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
Benny00
Asked:
Benny00
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
spcmnspffCommented:
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
 
sparcuCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Brendt HessSenior DBACommented:
Personally, I use the innate Round function:

Select Round(@Var1/@Var2, 3) -- Round to 3 Decimal Places
0
 
Benny00Author Commented:
Thanks for all the help everyone.  Angellll gave me the first one that worked.  Thanks again.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i must admit that the other suggestions where ALL better than mine :-(
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now