CODEWITHSUNDEEP
sql-servert-sqlrounding
Bahar Akhtarian
Bahar Akhtarian

Reputation: 107

Rounding down decimal numbers in SQL Server 2008

I read all rounding functions of T-SQL like Round, Floor, and Ceil, but none of them has rounded down decimal numbers correctly for me.

I have 2 questions:

  1. How to round down a decimal number (3.69 ==> 3.5)?
  2. How to round up the last 3 digits of an integer (e.g. 142600 ==> 143000)?

Upvotes: 9

Views: 34793

Answers (3)

ajit singh
ajit singh

Reputation: 1

The Oracle/PLSQL FLOOR function returns the largest integer value that is equal to or less than a number. eg: 

FLOOR(7.9)
Result: 7

FLOOR(30.29)
Result: 30

FLOOR(-7.9)
Result: -8

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

As per @J0e3gan 's anwser, Sql Server's Round allows rounding to the nearest powers of 10 using the length parameter, where length is 10^(-length), e.g. 

length = 0 : 10 ^ 0 = nearest 1
length = 3 : 10 ^ -3 = nearest .001
length = -3 : 10 ^ 3 = nearest 1000

etc

However, in general, with a simple 1-based rounding function - e.g. (Sql Round with Length=0) to round to an arbitrary value of "nearest N" - with the formula:

round(X / N) * N

e.g. nearest 100

select round(12345 / 100.0, 0) * 100.0 -- 12300
select round(-9876 / 100.0, 0) * 100.0 -- -9900
select round(-9849 / 100.0, 0) * 100.0 -- -9800

... Nearest 0.5

select round(5.123 / 0.5, 0) * 0.5 -- 5.000
select round(6.499 / 0.5, 0) * 0.5 -- 6.500
select round(-4.499 / 0.5, 0) * 0.5 -- -4.50

... Nearest 0.02

select round(5.123 / .02, 0) * .02 -- 5.12
select round(-9.871 / .02, 0) * .02 -- -9.88

etc

Remember that the type used for the divisors must be numeric / decimal or float.

Upvotes: 1

J0e3gan
J0e3gan

Reputation: 8938

1) select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) handles the first case - courtesy of an answer to a similar question on SQL Server Forums, which I adapted and quickly checked.

Note that if the numbers you are rounding to the nearest 0.5 could be bigger (e.g. 333.69 => 333.5), be sure to specify more decimal precision when you cast (e.g. select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1))), or you could get an overflow error:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Extra precision will not affect the bottom-line result (i.e. select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1)) and select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) both yield 3.5); but it is wasteful if the numbers you are rounding will always be smaller.

Online references with examples are available for T-SQL FLOOR, CAST, and decimal to help.

2) select ROUND(142600, -3) handles the second case.

A similar online reference is available for T-SQL ROUND.

Upvotes: 7

Related Questions