The SQL CEIL function and FLOOR function are opposites of each other and are very useful functions when dealing with numbers. Learn what they are and see some examples in this article.
Purpose
The purpose of the SQL CEIL function is to return the smallest integer value greater than or equal to the supplied number.
It essentially rounds up to the nearest whole number and is the opposite of the FLOOR function (which rounds down to the nearest whole number).
The word CEIL is short for CEILING.
The purpose of the SQL FLOOR function is to return the highest integer value that is equal to or less than a number. It basically rounds down to a whole number.
It’s the opposite of the CEILING function and is often used as an alternative to ROUND.
These functions exist in Oracle, SQL Server, MySQL, and Postgres.
SQL CEIL Syntax and Parameters
The syntax of the SQL CEIL function is simple:
CEIL ( input_number )
The parameters of the CEIL function are:
- input_number (mandatory): This is the value to convert or round up to an integer. It can be any numeric data type.
The return type of CEIL is the same data type as the input_number.
SQL FLOOR Syntax and Parameters
The syntax of the FLOOR function is:
FLOOR ( input_number )
The parameters of the FLOOR function are:
- input_number (mandatory): The value used to round down to the nearest whole number.
The return type is the same data type as the input_number.
Can You Use SQL CEIL and FLOOR With Decimal Numbers?
Yes, you can use CEIL and FLOOR with decimal numbers.
In fact, I would think it’s one of the most common uses of these functions.
I’ve used it many times to round decimal numbers up or down to a whole number.
See the Examples section for some examples of how to do this.
What’s the Difference Between FLOOR and CEILING in SQL?
The difference is quite small. While FLOOR rounds down to the nearest number, CEILING rounds up.
Both functions are helpful and work in a similar way.
Examples of the SQL CEIL and FLOOR Function
Here are some examples of the CEIL and FLOOR functions.
Example 1 – Rounded Up
This example uses CEIL and FLOOR on a number that would normally be rounded up.
SELECT
CEIL(41.8) AS ceil_test,
FLOOR(41.8) AS floor_test;
Result:
CEIL | FLOOR |
42 | 41 |
The result is 42 for CEIL because the number has been rounded up, and 41 for FLOOR because it has been rounded down.
Example 2 – Rounded Down
This example uses CEIL and FLOOR on a number that would normally be rounded down.
SELECT
CEIL(41.1) AS ceil_test,
FLOOR(41.1) AS floor_test;
Result:
CEIL | FLOOR |
42 | 41 |
The result is 42 for CEIL, because CEIL always rounds up, and not down. FLOOR shows 41, for the same reason – it always rounds down.
Example 3 – Two Decimal Places
This example uses a number with two decimal places.
SELECT
CEIL(102.43) AS ceil_test,
FLOOR(102.43) AS floor_test;
Result:
CEIL | FLOOR |
103 | 102 |
The result for CEIL is 103 because CEIL rounds up, and for FLOOR it is 102.
Example 4 – Lot of Decimal Places
This example uses a number with a lot of decimal places.
SELECT
CEIL(15.612093) AS ceil_test,
FLOOR(15.612093) AS floor_test;
Result:
CEIL | FLOOR |
16 | 15 |
The result is 16 for CEIL and 15 for FLOOR, as the number has been rounded up for CEIL and down for FLOOR.
Example 5 – Whole Number
This example uses a whole number.
SELECT
CEIL(19) AS ceil_test,
FLOOR(19) AS floor_test;
Result:
CEIL | FLOOR |
19 | 19 |
The result is 19 in both cases. The number has not been rounded as it is already an integer
Example 6 – Negative Number
This example uses a negative number.
SELECT
CEIL(-24.62) AS ceil_test,
FLOOR(-24.62) AS floor_test;
Result:
CEIL | FLOOR |
24 | 25 |
For CEIL, the result is 24 because the number has been rounded up. In this case, the next highest integer is 24, and not 25.
For FLOOR, the result is 25 because the number is rounded down.
Similar Functions
Some functions which are similar to the CEIL function are:
- ROUND – This function rounds a number to the nearest whole number, which can be higher or lower.
- TRUNC – This function also rounds a number, but removes digits from the number.
You can find a full list of Oracle SQL functions here.
Ben, I have pleased to read about Oracle CEIL and FLOOR Function Guide and Examples. It is really a helpful post. Great article thanks and keep it up!
Ben,
Love u man..
U make the concepts so easy for everyone by examples and simple language.
Keep on doing Ben..
Good Work..
Really Helpful …
Thanks, glad you like it!
Thanks, Ben. I only signed up on 06 May this year and have been using the contents of your articles every week since. It has been a great help to me. Much appreciated. I would really appreciate any previous articles you published. Cheers.
Hi Chris,
Glad you like the articles! I’ve got quite a few on the website. A good place to start would be the SQL Roadmap.
Thanks,
Ben