Oracle FLOOR Function: A Simple Guide

The FLOOR function in Oracle SQL is a mathematical function that returns the largest integer equal to or less than a given number. In simple terms, it always rounds a number down to the nearest whole number.

This is the direct opposite of the CEIL function, which always rounds up.

What is the FLOOR Function in Oracle?

The FLOOR function "pushes" any non-integer value down to the integer below it.

• FLOOR(5.8) returns 5.
• FLOOR(5.1) also returns 5.
• FLOOR(5) (an integer) just returns 5.
• Be careful with negative numbers: FLOOR(-5.8) returns -6, because -6 is the largest integer that is less than -5.8.

FLOOR Function Syntax

The syntax for FLOOR is very simple:

FLOOR(n)

Let's break that down:

• n: The number you want to round down. This can be a number, or a column that contains numeric data.

Oracle FLOOR Function Examples

Here are two practical examples of how to use FLOOR.

Example 1: Rounding Down a Positive Number with FLOOR

This example shows how FLOOR handles a positive number with a decimal. It will truncate the decimal part, effectively rounding down.

Query:

SELECT 
  FLOOR(29.95) AS "Floor_Value"
FROM DUAL;

Result:

Floor_Value
-----------
         29

Even though 29.95 is very close to 30, FLOOR rounds it down to 29.

Example 2: Rounding Down a Negative Number using FLOOR

This example shows how FLOOR works with negative numbers, which can be tricky. It finds the integer that is less than the input number.

Query:

SELECT 
  FLOOR(-4.2) AS "Negative_Floor"
FROM DUAL;

Result:

Negative_Floor
--------------
            -5

The result is -5, not -4, because -5 is the largest integer that is "equal to or less than" -4.2 on the number line.