How to Round Up and Round Down with MSSQL

Often, we will need to control how the rounding is done. We 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.

Rounding up and rounding down

DECLARE @num1 FLOAT;

DECLARE @num2 FLOAT;

SET @num1 = 123.12345;

SET @num2 = 12345.98765;

SELECT @num1 AS 'Number 1', @num2 AS 'Number 2',

CAST(@num2 AS INTEGER) AS 'Integer', FLOOR(@num2) AS 'Floor/round down',

CEILING(@num2) AS 'Celing/round up'

go

Result:

Number 1 Number 2 Integer Floor/round down Celing/round up

------------ ---------------- ----------- --------------------- ----------------

123.12345 12345.98765 12345 12345 12346

(1 row(s) affected)

Rounding up and rounding down to a specified length

DECLARE @num1 FLOAT;

DECLARE @num2 FLOAT;

SET @num1 = 123.12345;

SET @num2 = 12345.98765;

SELECT @num1 AS 'Number 1',

@num2 AS 'Number 2',

ROUND(@num1,2) AS 'Rounded #1 2 digits',

ROUND(@num2,2) AS 'Rounded #2 2 digits',

ROUND(@num1,-1) AS 'Rounded #2 0 digit',

ROUND(@num2,-3) AS 'Rounded #2 0 digit'

go

Result:

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)

Notes:

• 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.