Skip to content
Related Articles

Related Articles

Improve Article

FLOOR() AND CEIL() Function in MySQL

  • Last Updated : 28 Sep, 2020

1. FLOOR() Function :
FLOOR() function in MySQL is used to return the largest integer value which will be either equal to or less than from a given input number.

Syntax :

FLOOR(X)

Parameter : Required.
X : A number whose floor value we want to calculate.
Returns : 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 :

XX_Floor
90.5590
00
-9-9
-45.76-46
0.250


2. CEIL() Function :
CEIL() function in MySQL is used to return the smallest integer value which is either greater than or equal to the given input number.

Syntax :

CEIL(X)

Parameter : Required.
X : A number whose ceiling value we want to calculate.
Returns : 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 :

XX_Ceil
8.59
11
00
-1-1
-1.5-1
My Personal Notes arrow_drop_up
Recommended Articles
Page :