CEILING and FLOOR functions

CEILING function

CEILING function returns the smallest integer greater than, or equal to, the specified numeric expression.

The following query

SELECT 6.28 val, CEILING(6.28) pos_val, CEILING(-6.28) neg_val

The result returned by the function is of the same data type as that of an argument.

But the example been considered wouldn’t seem to confirm what was said above. Moreover, even doing explicit conversion of data type of the argument to exact numeric, we get integers (in Management Studio) except the money constant:

SELECT CEILING(CAST(6.28 as DEC(6,2))) ex_num,
CEILING(CAST(6.28 as FLOAT)) apr_num, CEILING($6.28) money_num

When using other client applications/drivers, you could get quite another representation of the result. If you execute this query in the tutorial directly, what result are you obtaining?

It may be said that format of result is just only “circumstantial evidence” for conclusion about result data type. More reliable approach is bytes occupied in memory by a value.

SELECT DATALENGTH(6.28) val, DATALENGTH(CEILING(6.28)) num_val,
DATALENGTH(CAST(CEILING(6.28) as INT)) int_val

The result of CEILING function has the same size in memory as the argument has, but the result been converted to the integer data type occupies 4 bytes only.

FLOOR function

The FLOOR function, on the contrary, returns the largest integer less than or equal to the specified numeric expression.

SELECT 6.28 val, FLOOR(6.28) pos_val, FLOOR(-6.28) neg_val