Online Tech Support

This online tutorial page is based on examples. The Oracle Floor function allows to round a number value down and the function removes all numbers after the decimal place. The Oracle Floor syntax is following:

FLOOR (<number>);

The first Oracle Floor example has input value 11.9999999 and the Oracle Round function would return number 12 but since Oracle Floor rounds the number value down it returns an integer value number 11. The Floor function always removes all numbers after a decimal place.

 SELECT FLOOR (11.9999999)
   FROM dual;

The second example shows there is no difference in the output value even when the input number is in this time 11.11111.

 SELECT FLOOR (11.11111)
   FROM dual;

The third Floor example applies on number 11.00 and it returns an integer 11 once again.

 SELECT FLOOR (11.00)
   FROM dual;

To demonstrate how to round down and to leave some numbers after the decimal place 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 example below the SQL output value is 11.988 and this is not rounded down, so the value does not fill our needs.

 SELECT ROUND (11.9876543,3)
   FROM dual;

When we are going to use Oracle Floor function it will remove all decimal places and to keep them we need to move the decimal place 3 places down. To do so we will multiply the 11.9876543 value with 1000. Take a look at the output number 11.9876543 became 11987.6543 and we are ready to apply 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 result value 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 Ceil Oracle Round Oracle Select Oracle To_Number Oracle Trunc