SQL CEIL and FLOOR Function Guide, FAQ, and Examples

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.