Open In App

FLOOR() AND CEIL() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 18 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads