SQL FLOOR Vs CEILING Vs ROUND Function

We shall discuss the main differences between FLOOR, CEILING, and ROUND functions in this article.

When we need to round off some decimal numbers in SQL queries, we constantly wonder which option to choose, even though there are three different types of system-defined SQL rounding functions: CEILING, ROUND, FLOOR.


SQL FLOOR Function

To get right-hand decimal value and return the greatest integer that is less than or equal to the supplied values (only number).

Example: Consider the following example

The FLOOR() function returns the largest integer value that is not greater than given value.

MySql,SQlserver,Ms Access

Select FLOOR(-4.32) As FloorValue;

Here given value is -6.43.floor function return the value that is not greater than given value

Output: The output of floor function is

FloorValue
-5

SQL CEILING Function

Returns the lowest integer larger or equal to the provided values. Get the value on the right side of the decimal.

Example: Consider following example to understand ceil function

The CEILING() function returns the smallest integer value that is not smaller than given number.

Select CEILING(69.34) As 'CeilValue';

In above query input is 69.34,Through ceil function output cannot be smaller than given value

Output: The output of ceil function is

CeilValue
70


SQL ROUND Function

The second input D and the number itself (digit after D decimal places >=5 or not) determine whether the ROUND() function rounds the number up or down.

Example 1:If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Consider the following example −

Mysql,Sql-server,Ms access

SELECT ROUND(743.78653) AS RoundValue;

Here, after decimal we have valuea above 5 so given value will be rounded up

Output: The output will be:

RoundValue
744

SQL Difference Between FLOOR ,CEIL And ROUND Functions

The main distinguish in floor,ceil and round function points are tabulated:-

ROUND FLOOR CEILING
The second argument and the number itself determine whether the ROUND() function rounds the number up or down. The FLOOR() function rounds the number to the nearest tenth of a value The CEILING() method always rounds numbers up, away from zero.
ROUND Accepts three values and rounds a positive or negative value to a particular length. FLOOR Returns the greatest integer less than or equal to the provided numeric expression and accepts one value after evaluating the value on the right side of the decimal. CEILING Returns the lowest integer larger than or equal to the provided numeric expression and accepts one value after evaluating the value on the right side of the decimal.
The round() method applies the rounding principle to a numeric field, allowing it to be rounded to a specified number of decimal places. The floor (value) function returns the smallest integer that is less than or equal to the value supplied. The smallest integer larger than or equal to the supplied value is returned by the Ceiling (value) function.