CEILING (SQL)

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

Synopsis

CEILING(numeric-expression)

{fn CEILING(numeric-expression)}

Description

CEILING returns the nearest integer value greater than or equal to numeric-expression. The returned value has 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()Opens in a new tab method call:

$SYSTEM.SQL.Functions.CEILING(numeric-expression)

Arguments

numeric-expression

A number whose ceiling is to be calculated. The number can be either a literal or a string. The number can be specified in scientific notation.

If numeric-expression is of a numeric type, CEILING returns the same data type as numeric-expression.

Examples

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,
       CEILING(167.0) AS CeilingNum4

all return 168.

SELECT CEILING(-167.111) AS CeilingNum1,
       CEILING('-167.456') AS CeilingNum2,
       CEILING(-167.999) AS CeilingNum3,
       CEILING(-167.0) AS CeilingNum4

all return -167.

The following examples use scientific notation:

SELECT CEILING(10E-1) // returns 1
SELECT CEILING('-14E-4') // returns 0
SELECT CEILING('-10E-1') // returns -1

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
ORDER BY CeilingNum DESC