FLOOR() and CEILING() Function in SQL

The FLOOR() and CEILING() functions in SQL are mathematical functions used to convert numeric values to integers. FLOOR() returns the largest integer less than or equal to the number, while CEILING() returns the smallest integer greater than or equal to the number.

Syntax

FLOOR():

FLOOR(number)

CEILING():

CEILING(number)

• number: The numeric value that you want to round.

Note: FLOOR() and CEILING() are supported by MySQL, SQL Server, PostgreSQL and SQLite. In some database systems, CEILING() may also be written as CEIL().

Examples

Below are some examples of the FLOOR() and CEILING() functions to understand how they work with different numeric values.

Example 1: Use FLOOR() Function

In this example, we will use the FLOOR() function to round a decimal value down to the nearest integer.

Query:

SELECT FLOOR(25.9) AS floor_value;

Output:

• Rounds 25.9 down to 25.
• Returns the result as floor_value.

Example 2: Use CEILING() Function

In this example, we will use the CEILING() function to round a decimal value up to the nearest integer.

Query:

SELECT CEILING(25.1) AS ceiling_value;

Output:

• Rounds 25.1 up to 26.
• Returns the result as ceiling_value.

Example 3: Use FLOOR() and CEILING() on a Table Column

Let us create a products table containing product prices.

Now, we can use FLOOR() and CEILING() to round each product price.

Query:

SELECT product_name,
       price,
       FLOOR(price) AS floor_price,
       CEILING(price) AS ceiling_price
FROM products;

Output:

• FLOOR() rounds each price down to the nearest integer.
• CEILING() rounds each price up to the nearest integer.
• The original price column remains unchanged.