How to round up and round down with MSSQL

  • Often, you will need to control how the rounding is done. You can either round up or down down.
  • The standard thinking is that if the number is .5 and up, it should round up, and if it is less than .5 then round down. But what about .5?
  • Microsoft SQL Server provides a few ways of controlling the rounding with the functions: ROUND, FLOOR, & CEILING.

Applies to:

  • Microsoft SQL Server 7
  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Rounding up and rounding down

  1. DECLARE @num1 FLOAT;
  2. DECLARE @num2 FLOAT;
  3. SET @num1 = 123.12345;
  4. SET @num2 = 12345.98765;
  5. SELECT @num1 AS 'Number 1',
  6.        @num2 AS 'Number 2',
  7.        CAST(@num2 AS INTEGER) AS 'Integer',
  8.        FLOOR(@num2) AS 'Floor/round down',
  9.        CEILING(@num2) AS 'Celing/round up'
  10. go
[Get Code]
Number 1               Number 2               Integer     Floor/round down       Celing/round up
---------------------- ---------------------- ----------- ---------------------- ----------------------
123.12345              12345.98765            12345       12345                  12346

(1 row(s) affected)
  • integer always drop the decimal portion, just like the floor.
  • floor always drop the decimal portion and returns the integer portion.
  • ceiling always drop the decimal portion and returns the integer portion then adds +1.

Rounding up and rounding down to a specified length

  1. DECLARE @num1 FLOAT;
  2. DECLARE @num2 FLOAT;
  3. SET @num1 = 123.12345;
  4. SET @num2 = 12345.98765;
  5. SELECT @num1 AS 'Number 1',
  6.        @num2 AS 'Number 2',
  7.        ROUND(@num1,2) AS 'Rounded #1 2 digits',
  8.        ROUND(@num2,2) AS 'Rounded #2 2 digits',
  9.        ROUND(@num1,-1) AS 'Rounded #2 0 digit',
  10.        ROUND(@num2,-3) AS 'Rounded #2 0 digit'
  11. go
[Get Code]
Number 1               Number 2               Rounded #1 2 digits    Rounded #2 2 digits    Rounded #2 0 digit     Rounded #2 0 digit
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
123.12345              12345.98765            123.12                 12345.99               120                    12000

(1 row(s) affected)
  • round will round to the specified length.
  • The rounding can be positive: it will round the decimal places.
  • The rounding can be negative: it will round the integer places.
  • The last digit is always an estimate.