FLOOR() AND CEIL() Function in MySQL
1. FLOOR() Function : The FLOOR() function in MySQL is a mathematical function that returns the largest integer value that is less than or equal to a given numeric expression. It rounds down a given numeric value to the nearest integer value that is less than or equal to the original value.
Syntax:
FLOOR(X)
Parameter : Required. X : A number whose floor value we want to calculate.
Return: It returns the closest integer which is <=X. So, if X is integer than it will return X. Otherwise, largest integer which is lesser than X.
Example-1 : Applying FLOOR() function to a +ve integer.
SELECT FLOOR(4) AS Floor_Value;
Output:
Floor_Value |
---|
4 |
Example-2: Applying FLOOR() function to a -ve integer.
SELECT FLOOR(-6) AS Floor_Value;
Output :
Floor_Value |
---|
-6 |
Example-3: Applying FLOOR() function to a +ve floating number.
SELECT FLOOR(1.5) AS Floor_Value;
Output :
Floor_Value |
---|
1 |
Example-4: Applying FLOOR() function to a -ve floating number.
SELECT FLOOR(-1.5) AS Floor_Value;
Output :
Floor_Value |
---|
-2 |
Example-5: FLOOR value of a numeric column in a table. Table – Number
X |
---|
90.55 |
0 |
-2 |
-45.76 |
0.25 |
SELECT X, FLOOR(X) AS X_Floor FROM Number;
Output:
X | X_Floor |
---|---|
90.55 | 90 |
0 | 0 |
-9 | -9 |
-45.76 | -46 |
0.25 | 0 |
2. CEIL() Function: The CEIL() function in MySQL is a mathematical function that returns the smallest integer value that is greater than or equal to a given numeric expression. It rounds up a given numeric value to the nearest integer value that is greater than or equal to the original value.
Syntax :
CEIL(X)
Parameter: Required. X : A number whose ceiling value we want to calculate.
Return Type: It returns the closest integer which is >=X. So, if X is integer than it will return X. Otherwise, next integer which is greater than X.
Example-1 : Applying CEIL() function to a +ve integer.
SELECT CEIL(5) AS Ceil_Value;
Output :
Ceil_Value |
---|
5 |
Example-2 : Applying CEIL() function to a -ve integer.
SELECT CEIL(-8) AS Ceil_Value;
Output :
Ceil_Value |
---|
-8 |
Example-3 : Applying CEIL() function to a +ve floating number.
SELECT CEIL(1.5) AS Ceil_Value;
Output :
Ceil_Value |
---|
2 |
Example-4 : Applying CEIL() function to a -ve floating number.
SELECT CEIL(-1.5) AS Ceil_Value;
Output :
Ceil_Value |
---|
-1 |
Example-5 : CEIL value of a numeric column in a table. Table – Number
X |
---|
8.5 |
1 |
0 |
-1 |
-1.5 |
SELECT X, CEIL(X) AS X_Ceil FROM Number;
Output :
X | X_Ceil |
---|---|
8.5 | 9 |
1 | 1 |
0 | 0 |
-1 | -1 |
-1.5 | -1 |
Contact Us