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:
Example-2: Applying FLOOR() function to a -ve integer.
SELECT FLOOR(-6) AS Floor_Value;
Output :
Example-3: Applying FLOOR() function to a +ve floating number.
SELECT FLOOR(1.5) AS Floor_Value;
Output :
Example-4: Applying FLOOR() function to a -ve floating number.
SELECT FLOOR(-1.5) AS Floor_Value;
Output :
Example-5: FLOOR value of a numeric column in a table. Table – Number
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 :
Example-2 : Applying CEIL() function to a -ve integer.
SELECT CEIL(-8) AS Ceil_Value;
Output :
Example-3 : Applying CEIL() function to a +ve floating number.
SELECT CEIL(1.5) AS Ceil_Value;
Output :
Example-4 : Applying CEIL() function to a -ve floating number.
SELECT CEIL(-1.5) AS Ceil_Value;
Output :
Example-5 : CEIL value of a numeric column in a table. Table – Number
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 |
Last Updated :
18 Apr, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...