Oracle FLOOR() Function

Oracle FLOOR() is a built-in function that returns the largest integer value less than or equal to the specified number.

Oracle FLOOR() syntax

Here is the syntax for the Oracle FLOOR() function:

FLOOR(num)

Parameters

num

Required. It can be any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

Return Value

The Oracle FLOOR() function returns the largest integer value less than or equal to the specified number. The function returns the same data type as the parameter’s numeric data type.

If any parameter is NULL, FLOOR() will return NULL.

Oracle FLOOR() Examples

Here are some examples that demonstrate the usage of the Oracle FLOOR() function.

Basic Usage

SELECT
    FLOOR(7.2),
    FLOOR(-7.2)
FROM dual;

Output:

   FLOOR(7.2)    FLOOR(-7.2)
_____________ ______________
            7             -8

FLOOR() vs CEIL()

The following statement demonstrates the difference between FLOOR() and CEIL():

SELECT
    FLOOR(7.2),
    CEIL(7.2),
    FLOOR(-7.2),
    CEIL(-7.2)
FROM dual;

Output:

   FLOOR(7.2)    CEIL(7.2)    FLOOR(-7.2)    CEIL(-7.2)
_____________ ____________ ______________ _____________
            7            8             -8            -7

FLOOR() vs ROUND()

The following statement demonstrates the difference between FLOOR() and ROUND():

SELECT
    FLOOR(7.2),
    ROUND(7.2),
    FLOOR(7.6),
    ROUND(7.6)
FROM dual;

Output:

   FLOOR(7.2)    ROUND(7.2)    FLOOR(7.6)    ROUND(7.6)
_____________ _____________ _____________ _____________
            7             7             7             8

NULL Parameters

If any parameter is NULL, FLOOR() will return NULL.

SET NULL 'NULL';
SELECT
    FLOOR(NULL)
FROM dual;

Output:

   FLOOR(NULL)
______________
          NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle FLOOR() is a built-in function that returns the largest integer value less than or equal to the specified number.