SQL Server 101 – How to Round Up or Round Down a Number ?

In SQL Server, you may want to round up or round down a number to find the ceiling or floor value. You can use the CEILING and FLOOR function in SQL Server to achieve this.

The ROUND function rounds a numeric value to a specified precision. The syntax of the ROUND function is as follows:

ROUND ( numeric_expression , length [ ,function ] )

• numeric_expression: the numeric value to be rounded.
• length: the number of decimal places to which the value should be rounded.
• function: an optional argument that specifies the type of rounding to perform. The default value is 0, which indicates that the value should be rounded to the nearest whole number.

You can also check the CEILING AND FLOOR function in SQL Server which performs the rounding up and rounding down respectively.

How to Round Up or Round Down a Number in SQL Server?

Here’s a SQL query that demonstrates the usage of ROUND,CEILING and FLOOR function in SQL server.

DECLARE @input decimal(18,5)
SET @input = 78.78912
SELECT CEILING(@input) CeilingValue
SELECT FLOOR(@input) FloorValue
SELECT ROUND(@input, 2) RoundValue

In the above query , we were successfully be able to use the CEILING and FLOOR function to get the rounded up or rounded down value. Remember that the CEILING function and FLOOR is very different from the ROUND function as the ROUND function uses the rounding(ceiling/floor) function under the hood.