In Oracle, the FLOOR() function returns the largest integer equal to or less than its argument.

Syntax

The syntax goes like this:

FLOOR(n)

Where n can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Example

Here’s an example:

SELECT FLOOR(1.85)
FROM DUAL;

Result:

   FLOOR(1.85) 
______________ 
             1 

Switching it to a negative value produces the following result:

SELECT FLOOR(-1.85)
FROM DUAL;

Result:

   FLOOR(-1.85) 
_______________ 
             -2 

Compared to ROUND()

The FLOOR() function is different to the ROUND() function. The ROUND() function would round the number up in some instances, and down in others. FLOOR() on the other hand, always returns the largest integer equal to or less than its argument.

SELECT 
    FLOOR(1.85),
    ROUND(1.85)
FROM DUAL;

Result:

   FLOOR(1.85)    ROUND(1.85) 
______________ ______________ 
             1              2 

Also, ROUND() allows us to specify the number of decimal places to round to:

SELECT 
    FLOOR(1.85),
    ROUND(1.85, 1)
FROM DUAL;

Result:

   FLOOR(1.85)    ROUND(1.85,1) 
______________ ________________ 
             1              1.9 

Non-Numeric Argument

The argument can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Here’s what happens when we provide a non-numeric argument that can’t be converted to a numeric data type:

SELECT FLOOR('Bruce')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT FLOOR('Bruce')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Values

Passing null to FLOOR() returns null:

SET NULL 'null';

SELECT FLOOR(null)
FROM DUAL;

Result:

   FLOOR(NULL) 
______________ 
          null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling FLOOR() without passing any arguments returns an error:

SELECT FLOOR()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT FLOOR()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT FLOOR(1, 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT FLOOR(1, 2)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

Oracle also has a CEIL() function that returns the smallest integer that is greater than or equal to its argument.