The Oracle CEIL function and FLOOR function are opposites of each other and are very useful functions when dealing with numbers. Learn what they are and see some examples in this article.

Purpose

The purpose of the Oracle CEIL function is to return the smallest integer value greater than or equal to the supplied number.

It essentially rounds up to the nearest whole number, and is the opposite of the FLOOR function (which rounds down to the nearest whole number).

The word CEIL is short for CEILING.

The purpose of the Oracle FLOOR function is to return the highest integer value that is equal to or less than a number. It basically rounds down to a whole number.

It’s the opposite of the CEILING function, and is often used as an alternative to ROUND.

 

Oracle CEIL Syntax and Parameters

The syntax of the Oracle CEIL function is simple (and described on the Oracle documentation):

CEIL ( input_val )

The parameters of the Oracle CEIL function are:

  • input_val (mandatory): This is the value to convert or round up to an integer. It can be any numeric data type.

The return type of CEIL is the same data type as the input_val.

 

Oracle FLOOR Syntax and Parameters

The syntax of the FLOOR function is:

FLOOR ( input_number )

The parameters of the FLOOR function are:

  • input_number (mandatory): The value use to round down to the nearest whole number.

The return type is the same data type as the argument.

 

Can You Use Oracle CEIL and FLOOR With Decimal Numbers?

Yes, you can use CEIL and FLOOR with decimal numbers. In fact, I would think it’s one of the most common uses of these functions.

I’ve used it many times to round decimal numbers up or down to a whole number.

See the Examples section for some examples on how to do this.

 

What’s the Difference Between FLOOR and CEILING in Oracle?

The difference is quite small. While FLOOR rounds down to the nearest number, CEILING rounds up.

Both functions are helpful and work in a similar way.

 

Examples of the Oracle CEIL and FLOOR Function

Here are some examples of the CEIL and FLOOR functions. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – Rounded Up

This example uses CEIL and FLOOR on a number that would normally be rounded up.

SELECT

CEIL(41.8) AS ceil_test,

FLOOR(41.8) AS floor_test

FROM dual;

Result:

CEIL FLOOR
42 41

The result is 42 for CEIL because the number has been rounded up, and 41 for FLOOR because it has been rounded down.

 

Example 2 – Rounded Down

This example uses CEIL and FLOOR on a number that would normally be rounded down.

SELECT

CEIL(41.1) AS ceil_test,

FLOOR(41.1) AS floor_test

FROM dual;

Result:

CEIL FLOOR
42 41

The result is 42 for CEIL, because CEIL always rounds up, and not down. FLOOR shows 41, for the same reason – it always rounds down.

 

Example 3 – Two Decimal Places

This example uses a number with two decimal places.

SELECT

CEIL(102.43) AS ceil_test,

FLOOR(102.43) AS floor_test

FROM dual;

Result:

CEIL FLOOR
103 102

The result for CEIL is 103 because CEIL rounds up, and for FLOOR it is 102.

 

Example 4 – Lot of Decimal Places

This example uses a number with a lot of decimal places.

SELECT

CEIL(15.612093) AS ceil_test,

FLOOR(15.612093) AS floor_test

FROM dual;

Result:

CEIL FLOOR
16 15

The result is 16 for CEIL and 15 for FLOOR, as the number has been rounded up for CEIL and down for FLOOR.

 

Example 5 – Whole Number

This example uses a whole number.

SELECT

CEIL(19) AS ceil_test,

FLOOR(19) AS floor_test,

FROM dual;

Result:

CEIL FLOOR
19 19

The result is 19 in both cases. The number has not been rounded as it is already an integer

 

Example 6 – Negative Number

This example uses a negative number.

SELECT

CEIL(-24.62) AS ceil_test,

FLOOR(-24.62) AS floor_test

FROM dual;

Result:

CEIL FLOOR
24 25

For CEIL, the result is 24 because the number has been rounded up. In this case, the next highest integer is 24, and not 25.

For FLOOR, the result is 25 because the number is rounded down.

 

Similar Functions

Some functions which are similar to the CEIL function are:

  • ROUND – This function rounds a number to the nearest whole number, which can be higher or lower.
  • TRUNC – This function also rounds a number, but removes digits from the number.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of digitalart / FreeDigitalPhotos.net

Improve Your Oracle SQL With My 10-Day Email Course

x