CEILING (Transact-SQL) And CEIL (Oracle SQL)

Returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax

Sql Server:

            CEILING (numeric_expression)

Oracle:

            CEIL (numeric_expression)

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype.

Returns the same type as numeric_expression.

SQL SERVER 2008 Example:

The following example shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING ($123.45), CEILING ($-123.45), CEILING ($0.0)

Here is the result set.

--------- --------- -------------------------

124.00    -123.00    0.00

Because the argument data type is money hence result set has two decimal places.

SELECT CEILING (123.45), CEILING (-123.45), CEILING (0.0)

CEIL (123.45)         CEIL (-123.45)     CEIL(0.0)

------------------------------------------------------

124                          -123                  0

Oracle Example:

SELECT CEIL (123.45), CEIL (-123.45), CEIL (0.0) from dual

CEIL (123.45)         CEIL (-123.45)     CEIL(0.0)

------------------------------------------------------

124                          -123                  0

FLOOR (Transact-SQL and Oracle SQL)

Returns the largest integer less than or equal to the specified numeric expression.

Syntax

            FLOOR ( numeric_expression )

Arguments

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype.

Return Types

Returns the same type as numeric_expression.

Sql Server Example:

The following example shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR (123.45), FLOOR (-123.45), FLOOR ($123.45)

-------------------------------------------------------------------

                123                          -124              123.0000

Oracle Example:

SELECT FLOOR (15.7) "Floor" FROM DUAL;

Result: