# How To Use FLOOR In Oracle SQL

## May 12, 2021 | ## Oracle DBA |

This help is based on examples so it would be easier to understand. Oracle Floor function allows to round a number down and the Floor function removes all numbers after the decimal point. The syntax of Oracle Floor is:

FLOOR (<number>);

The following Oracle Floor example has input value “**11.9999999**” and function Oracle Round would have returned number “**12″** but since Oracle Floor rounds the number down it returns an integer value number “**11**“. The function always removes all numbers after the decimal point.

SELECT FLOOR (11.9999999) FROM dual;

The second example shows that there is no difference in the output value from the example above even when the input number is “**11.11111**“. Oracle Floor does round down and it returns “**11**” again.

SELECT FLOOR (11.11111) FROM dual;

The third Oracle Floor example applies on number “**11.00**” and it returns an integer “**11**” once again. The function job is this time to remove the nils.

SELECT FLOOR (11.00) FROM dual;

To demonstrate how to round down and to leave some numbers after the decimal point we are going to use number “**11.9876543**” and we will keep *3 numbers after the decimal place*. First lets take a look why Oracle Round does not suit here. Take a look at the SQL example below the output is “**11.988**” and this is not rounded down. The “**11.987…**” has become “**11.988**“, so the number achieved by Oracle Round does not fill our needs. That does mean it didn’t round the number down.

SELECT ROUND (11.9876543,3) FROM dual;

When we are going to use Oracle Floor function it removes all numbers after the decimal point and to keep them we need to move the decimal point *3 places down*. To do so we will multiply the** “11.9876543**” value with “**1000**“. Take a look at the output. The number “**11.9876543**” has become “**11987.6543**” and we are ready to use Oracle Floor on it.

SELECT 11.9876543 * 1000 FROM dual;

As we mentioned above we will use Oracle Floor function on the “**11987.6543**” number and after removing the decimal places the value becomes “**11987**“.

SELECT FLOOR(11987.6543) FROM dual;

To move the number on its original look we need to divide number “**11987**” with “**1000**” and the final number is “**11.987**” which also is rounded down.

SELECT 11987 / 1000 FROM dual;

Now all this hassle above can be skip if you’ll use function Oracle Trunc. This function truncates (read rounds down) the input value depending on the second parameter. Take a look at the output it is same “**11.987**” as at the example above.

SELECT TRUNC (11.9876543,3) FROM dual;

To read more about Oracle Trunc, Round or Ceil take a look at the links below.

**See Also:**

Oracle Select Oracle Round Oracle Trunc Oracle Ceil Home