Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous sectionNext section


A numeric function that returns the smallest integer greater than or equal to a given numeric expression.


{fn CEILING(numeric-expression)}


Argument Description
numeric-expression A number whose ceiling is to be calculated.


CEILING returns the nearest integer value greater than or equal to numeric-expression. The returned value has the same data type as numeric-expression and a scale of 0. When numeric-expression is a NULL value, an empty string (''), or any nonnumeric string, CEILING returns NULL.

Note that CEILING can be invoked as an ODBC scalar function (with the curly brace syntax) or as an SQL general function.

This function can also be invoked from ObjectScript using the CEILING() method call:



The following examples show how CEILING converts a fraction to its ceiling integer:

SELECT CEILING(167.111) AS CeilingNum1,
       CEILING(167.456) AS CeilingNum2,
       CEILING(167.999) AS CeilingNum3
Copy code to clipboard

all return 168.

SELECT {fn CEILING(167.00)} AS CeilingNum1,
       {fn CEILING(167.00)} AS CeilingNum2
Copy code to clipboard

return 167.

SELECT CEILING(-167.111) AS CeilingNum1,
       CEILING(-167.456) AS CeilingNum2,
       CEILING(-167.999) AS CeilingNum3
Copy code to clipboard

all return -167.

SELECT CEILING(-167.00) AS CeilingNum 
Copy code to clipboard

returns -167.

The following example uses a subquery to reduce a large table of US Zip Codes (postal codes) to one representative city for each ceiling Latitude integer:

SELECT City,State,CEILING(Latitude) AS CeilingLatitude 
FROM (SELECT City,State,Latitude,CEILING(Latitude) AS CeilingNum
      FROM Sample.USZipCode)
GROUP BY CeilingNum
Copy code to clipboard

See Also