How To Use FLOOR In Oracle SQL

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