Back to Library

CEIL()/CEILING() in SQL

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Syntax

CEIL(number) or CEILING(number)
Return type
NUMERIC (or equivalent numeric type in the respective DBMS)

CEIL()/CEILING() Function Example

SELECT CEIL(123.45); 
-- Output: 124

What is CEIL()/CEILING() in SQL?

The CEIL() (or CEILING()) function in SQL rounds a given number up to the nearest integer. It always rounds away from zero for positive numbers and toward zero for negative numbers, ensuring the result is the smallest integer that is greater than or equal to the given value. This function is useful in mathematical calculations, financial applications, and data processing where rounding up is required. It is supported in MySQL, PostgreSQL, and Oracle as CEIL(), while SQL Server and some other databases use CEILING() for the same functionality.

Parameters:

  • number: The numeric value to be rounded up.

Example Use Cases:

-- Calculate minimum number of packages needed

SELECT CEIL(items_count/package_size) AS packages_needed FROM orders; 
-- Output:
packages_needed
--------------
3
5
2
4
7

-- Round up storage space requirements 

SELECT CEIL(file_size_mb) AS storage_needed FROM files; 
-- Output:
storage_needed
-------------
125
456
789
234
567

-- Calculate minimum number of work days 

SELECT CEIL(total_hours/8) AS work_days FROM projects;
-- Output:
work_days
---------
5
3
8
4
6

Notes:

  • Behavior: Always rounds up to next integer (e.g., 2.1 becomes 3, -2.1 becomes -2)

  • Performance Considerations: Basic numeric operation, highly efficient

  • Version Info: Core mathematical function available in all major DBMS

  • Deprecated/Recommended Alternatives: Some DBMS use CEILING() instead of CEIL()

Error Handling:

  • Error: Returns NULL if input is NULL

  • Recommendation: Use COALESCE if NULL handling needed

Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

CEIL() or CEILING()

CEIL(15.1)

Returns NULL

PostgreSQL

CEIL() or CEILING()

CEIL(15.1)

Returns NULL

SQL Server

CEILING()

CEILING(15.1)

Returns NULL

SQLite

CEIL()

CEIL(15.1)

Returns NULL

BigQuery

CEIL()

CEIL(15.1)

Returns NULL

Snowflake

CEIL() or CEILING()

CEIL(15.1)

Returns NULL

Athena

CEIL()

CEIL(15.1)

Returns NULL

Related Functions:

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

One place for all your queries,
directly on your SQL editor