FLOOR() AND CEIL() Function in MySQL

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 :

X	X_Floor
90.55	90
0	0
-9	-9
-45.76	-46
0.25	0

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 :

X	X_Ceil
8.5	9
1	1
0	0
-1	-1
-1.5	-1

My Personal Notes arrow_drop_up

Save