SQL Server CEILING() Function

In SQL Server, CEILING() returns the smallest integer greater than or equal to a numeric expression. For example, CEILING(-123.45) is -123. SQL Server determines the return type from the input; see Microsoft’s CEILING() reference.

Syntax

The syntax for CEILING() function is as follows:

CEILING ( numeric_expression )

Here, numeric_expression is the numeric expression to be rounded up.

Usage

Use CEILING() to round toward positive infinity. It accepts one numeric expression; to round to a multiple such as 10, scale the input and result, as in Example 2.

Return type

CEILING() does not always return an int; SQL Server determines the result type from the input:

If the result does not fit in the return type, SQL Server raises an arithmetic overflow error.

Examples

Here are two examples of using the CEILING() function:

Example 1

Suppose we have a products table Products that contains a column named Price, and we want to round up the prices of all products to the nearest integer. We can use the following query:

SELECT CEILING(Price) AS RoundedPrice
FROM Products

Suppose our products table looks like this:

The query result will be:

Example 2

Suppose we have a sales orders table SalesOrders that contains a numeric column named TotalAmount, and we want to round each amount up to the next multiple of $10. Using 10.0 keeps the division fractional if TotalAmount is an integer type:

SELECT CEILING(TotalAmount / 10.0) * 10.0 AS RoundedAmount
FROM SalesOrders

Suppose our sales orders table looks like this:

The query result will be:

Conclusion

CEILING() returns the smallest integer greater than or equal to its input. It has no scale or radix argument; scale the input explicitly when rounding to a multiple. For the opposite direction, see FLOOR(); for decimal-place rounding, see ROUND().