You are currently viewing Round Off Values Using Ceiling and Floor in SQL

Round Off Values Using Ceiling and Floor in SQL

Round Off Values Using Ceiling and Floor in SQL

Ceiling Function

CEILING() Function return the smallest integer value that is equal to or greater than the argument value. The arguments can be either positive or negative.


In the below example, 55.99 is the positive argument value and ceiling function will return the 56 because it the smallest integer value that is equal to or greater than the argument value 55.99.


Similarly, -55.99 is the negative argument value and it will return -55 because it is the smallest integer integer value which is equal to or greater than the argument value -55.99.

Ceiling Function with positive argument value

SELECT CEILING(55.99) AS CeilingValue
Ceiling Value with Positive Argument

Ceiling Function with positive argument value and variable

DECLARE @PositiveValue FLOAT
SET @PositiveValue = 99.1
SELECT CEILING(@PositiveValue) AS CeilingValue
Ceiling value with positive argument and variable

Ceiling Function with negative argument value

SELECT CEILING(-55.99) AS CeilingValue
ceiling value using negative argument

Ceiling Function with negative argument value and variable

DECLARE @NegativeValue FLOAT
SET @NegativeValue = -99.1
SELECT CEILING(@NegativeValue) AS CeilingValue
ceiling value using negative argument and variable

Floor Function

FLOOR() Function return the largest value that is equal to or less than the argument value. The arguments can be either positive or negative.

In below example, 55.99 is a positive value and it will return 55 because 55 is the largest value that is equal to or less than argument value 55.99.

Similarly, -55.99 is the negative value and it will return -56
because -56 is the largest negative value which is equal to or less than the argument value -55.99.

Floor Function with positive argument value

SELECT FLOOR(55.99) AS FloorValue
Floor with positive argument

Floor Function with positive argument value and variable

DECLARE @PositiveValue FLOAT
SET @PositiveValue = 99.1
SELECT Floor(@PositiveValue) AS FloorValue
Floor with positive value and variable

Floor Function with negative argument value

SELECT FLOOR(-55.99) AS FloorValue
Floor with negative argument

Floor Function with negative argument value and variable

DECLARE @NegativeValue FLOAT
SET @NegativeValue = -99.1
SELECT Floor(@NegativeValue) AS FloorValue
Floor with negative argument and variable

Recommended Readings