CEILING and FLOOR functions

CEILING function

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

The following query

  1. SELECT 6.28 val, CEILING(6.28) pos_val, CEILING(-6.28) neg_val
gives the results:

val pos_val neg_val
6.28 7 -6

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:

  1. SELECT CEILING(CAST(6.28 AS DEC(6,2))) ex_num,
  2. CEILING(CAST(6.28 AS FLOAT)) apr_num, CEILING($6.28) money_num

ex_num apr_num money_num
7 7 7,00

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.

  1. SELECT DATALENGTH(6.28) val, DATALENGTH(CEILING(6.28)) num_val,

val num_val int_val
5 5 4

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.

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

val pos_val neg_val
6.28 6 -7

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100