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