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
Return type
CEIL()/CEILING() Function Example
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
-- Round up storage space requirements
-- Calculate minimum number of work days
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:
FLOOR()
Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.
ROUND()
Rounds a number to a specified number of decimal places or to the nearest integer.
ABS()
Returns the absolute (positive) value of a number by removing the negative sign if present.
SIGN()
Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.