loading..
Ðóññêèé    English
18:51
go

CEILING and FLOOR functions

CEILING function

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

The following query

Console
Execute
  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:

Console
Execute
  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.

Console
Execute
  1. SELECT DATALENGTH(6.28) val, DATALENGTH(CEILING(6.28)) num_val, 
  2. DATALENGTH(CAST(CEILING(6.28) AS INT)) int_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.

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

val    pos_val   neg_val
6.28    6    -7
Tags
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.