Caché SQL Reference
FLOOR
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A numeric function that returns the largest integer less than or equal to a given numeric expression.
Synopsis
FLOOR(numeric-expression)
{fn FLOOR(numeric-expression)}
Arguments
numeric-expression A number whose floor is to be calculated.
Description
FLOOR returns the nearest integer value less 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 a nonnumeric string, FLOOR returns NULL.
Note that FLOOR 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 Caché ObjectScript using the FLOOR() method call:
$SYSTEM.SQL.FLOOR(numeric-expression)
Examples
The following examples show how FLOOR converts a fraction to its floor integer:
SELECT FLOOR(167.111) AS FloorNum1,
       FLOOR(167.456) AS FloorNum2,
       FLOOR(167.999) AS FloorNum3
 
all return 167.
SELECT {fn FLOOR(167.00)} AS FloorNum1,
       {fn FLOOR(167)} AS FloorNum2
 
return 167.
SELECT FLOOR(-167.111) AS FloorNum1,
       FLOOR(-167.456) AS FloorNum2,
       FLOOR(-167.999) AS FloorNum3
 
all return -168.
SELECT FLOOR(-167.00) AS FloorNum 
 
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 floor Latitude integer:
SELECT City,State,FLOOR(Latitude) AS FloorLatitude 
FROM (SELECT City,State,Latitude,FLOOR(Latitude) AS FloorNum
      FROM Sample.USZipCode)
GROUP BY FloorNum
ORDER BY FloorNum DESC
 
See Also