The SQL CEILING function returns the smallest integer value greater than or equal to the input value.
Description
The CEILING function evaluates the right side of the decimal value and returns an integer value that is least greater than or equal to the input value. CEILING is another SQL function for approximating numerical values like FLOOR and ROUND.
The CEILING is useful when you have a float or decimal value and you need to find the next lowest or highest integer.
The CEILING is the next highest integer value; whereas, floor is the next lowest.
CEILING and FLOOR have a practical use when you’re working with discrete quantities and averages.
For instance, let’s assume the sales manager is having a sales convention for all the sales people. They want to rent cars to be able to get around town. Assuming each car holds 4 people how many cars do he need to rent for the business trip?
To figure this out, we can take the number of sales people and divide by four
SELECT COUNT(*) / 4.0 as NumberCars FROM Sales.SalesPerson
This result is 4.25 cars. As you know you cannot rent nor drive a quarter of a car!
To get around this we need to round up to the nearest whole car. We can use CEILING to do so. Here is the query to use
SELECT CEILING(COUNT(*) / 4.0) as NumberCars
FROM Sales.SalesPerson
Also, did you notice I used 4.0 rather than 4 in the calculation? This is to ensure the result is a float; otherwise, the result is returned as an integer, and would have been implicitly converted to an integer value of 4.
SQL CEILING Usage Notes
The only argument is a numeric value to be approximated to the smallest greater value.
The data type of input expression is from either exact numeric or approximate numeric categories. The bit data type is invalid.
The return type is the same as the input value’s data type.
Syntax
CEILING (numeric_value)
SQL CEILING Examples
The following examples explain the use of the CEILING function for positive, negative, and zero input values.
In each case an integer is returned.
- The result for Example1 is intuitive. CEILING(22.3) is 23.
- However, notice Example2 returns -22 and not -23. Keep in mind -22 is greater than -23, and ceiling return the next greatest integer.
The output data type is similar to the input data type, money.
The following example uses the SQL CEILING function on the AdvetureWorks2019 database column. It returns the smallest value greater than or equal to the standard cost of each product.
