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.
Benny00Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
All Courses

From novice to tech pro — start learning today.