SQL CEIL, FLOOR Functions

SQL CEIL Function

SQL CEIL function return smallest integer round value that is greater then or equal to a parameter value.

Another way we can say next round value that value return of the function.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Example

Consider following example that return smallest integer round value that is greater then or equal to a number.

SQL> SELECT CEIL(2.5) "CEIL(2.5)" FROM DUAL;

 CEIL(2.5)
----------
         3

Example Explanation

  • we are pass into CEIL(2.5) function.
  • function find all largest round value (3, 4, 5, 6, ... n) from 2.5.
  • Now smallest integer value (3) that is greater then or equal to a number (2.5).
  • Function return 3 value.

More example

SQL> SELECT CEIL(11.25) "CEIL(11.25)" FROM DUAL;

CEIL(11.25)
-----------
         12

SQL FLOOR Function

SQL FLOOR function return largest integer round value that is equal to or less then to a parameter value.

Another way we can say last round value that value return of the function.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Example

Consider following example that return largest integer round value that is equal to or less then to a number.

SQL> SELECT FLOOR(6.5) "FLOOR(6.5)" FROM DUAL;

FLOOR(6.5)
----------
         6

Example Explanation

  • we are pass into FLOOR(6.5) function.
  • Function find all smallest round value (6, 5, 4, ... 0) from 6.5.
  • Now largest integer value (6) that is equal to or less then to a number (6.5).
  • Finally function return 6 value.