In SQLite, the ceil() and ceiling() functions are used to return the smallest integer that is greater than or equal to a given number. They both serve the same purpose, and either function can be used interchangeably.

Syntax

CEIL(number)
-- or
CEILING(number)

Where number is the numeric value that you want to apply the ceiling function to. The function will round this number up to the next whole number if it has any decimal places.

Example 1

Here’s a quick example to demonstrate how it works:

SELECT CEIL(4.2) AS Result1, 
       CEILING(5.7) AS Result2, 
       CEIL(-2.3) AS Result3, 
       CEILING(-8.9) AS Result4;

Output:

Result1  Result2  Result3  Result4
-------  -------  -------  -------
5.0      6.0      -2.0     -8.0   

Explanation:

• CEIL(4.2): Rounds up 4.2 to the next integer, which is 5.
• CEILING(5.7): Rounds up 5.7 to the next integer, which is 6.
• CEIL(-2.3): Rounds up -2.3 to the next integer, which is -2 (since -2 is the next integer greater than -2.3).
• CEILING(-8.9): Rounds up -8.9 to the next integer, which is 8.

Example 2

For this example, we’ll create a table called numbers that contains a column for the numeric values to apply the ceil() and ceiling() functions to. We’ll also insert some values:

CREATE TABLE numbers (
    id INTEGER PRIMARY KEY,
    value REAL
);

INSERT INTO numbers (value) VALUES
(3.14),
(7.89),
(-2.56),
(5.01),
(-4.99);

Now, let’s select the value column along with the results of ceil() and ceiling() functions:

SELECT value,
       CEIL(value) AS ceil_value,
       CEILING(value) AS ceiling_value
FROM numbers;

Output:

value  ceil_value  ceiling_value
-----  ----------  -------------
3.14   4.0         4.0          
7.89   8.0         8.0          
-2.56  -2.0        -2.0         
5.01   6.0         6.0          
-4.99  -4.0        -4.0         

Explanation:

• CEIL(3.14) and CEILING(3.14): Rounds 3.14 up to 4.
• CEIL(7.89) and CEILING(7.89): Rounds 7.89 up to 8.
• CEIL(-2.56) and CEILING(-2.56): Rounds -2.56 up to -2 (since -2 is the next greater integer).
• CEIL(5.01) and CEILING(5.01): Rounds 5.01 up to 6.
• CEIL(-4.99) and CEILING(-4.99): Rounds -4.99 up to -4.

As you can see, both functions produce the same result, as they are functionally equivalent in SQLite.