SQL CEILING() Function: The Complete Guide

The ceiling() function returns the smallest integer greater than, or equal to, the specified numeric expression.

SQL CEILING()

SQL CEILING() is a built-in function used to return the smallest integer value that is greater than or equal to a number given as input. The ceiling() function returns the smallest integer value greater than or equal to a number.

Syntax

SELECT CEILING (Number);

Parameters

Number: The number whose ceiling value is to be calculated.

Examples

Query 1

SELECT CEILING (2.5);

Output

3

Explanation

Here, the ceiling value is returned.

Query 2

SELECT CEILING (1.3);

Output

2

Explanation

Here, the ceiling value is returned.

Query 3

SELECT CEILING (-1.3);

Output

-1

Explanation

Here, the ceiling value is returned, which is negative.

 Do not confuse why the output is not -2. This is because, like the already mentioned function returns, the value is greater or equal to a number, so -1 is greater than -1.3 so, the output is -1.

Query 4

SELECT CEILING (25.75 + 5.0);

Output

31

Explanation

Here, the ceiling value of the expression is returned.

Above were the common examples to illustrate the meaning of the CEILING function.

Let’s apply this function on a table Employee.

Employee

Emp_id	First_name	City	State	Salary_per_month
101	Rohit	Patna	Bihar	25895.75
201	Shivam	Jalandhar	Punjab	30122.45
301	Karan	Allahabad	Uttar Pradesh	99872.15
401	Suraj	Kolkata	West Bengal	88789.88
501	Akash	Vizag	Andhra Pradesh	98985.77

 

Suppose we want to remove the decimal point from the salary_per_month, then the ceiling function can be applied to it.

Query

Select Emp_id, First_name, City, State, 
CEILING (Salary_per_month) AS Salary_per_month from Employee;

Output

Emp_id	First_name	City	State	Salary_per_month
101	Rohit	Patna	Bihar	25896
201	Shivam	Jalandhar	Punjab	30123
301	Karan	Allahabad	Uttar Pradesh	99873
401	Suraj	Kolkata	West Bengal	88790
501	Akash	Vizag	Andhra Pradesh	98986

 

Explanation

Here, you can see that salary_per_month values previously contained decimal values have been removed.

MySQL CEILING()

The CEILING() function in MySQL returns the smallest integer value bigger than or equal to a number.

PostgreSQL CEILING()

The PostgreSQL ceiling() returns the value after rounding up any positive or negative decimal value as greater than the argument.

Conclusion

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value greater than or equal to a number.

See also

SQL FLOOR()

SQL LOG10()

SQL PI

SQL POWER()

SQL DEGREES()