Related Articles

# 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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up