FLOOR (SQL)

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)}

Description

FLOOR returns the nearest integer value less 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 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 ObjectScript using the FLOOR()Opens in a new tab method call:

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

Arguments

numeric-expression

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

If numeric-expression is of a numeric type, FLOOR returns the same data type as 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,
       FLOOR(167.0) AS FloorNum4

all return 167.

SELECT FLOOR(-167.111) AS FloorNum1,
       FLOOR(-167.456) AS FloorNum2,
       FLOOR(-167.999) AS FloorNum3,
       FLOOR(-168.0) AS FloorNum4

all return -168.

The following examples use scientific notation:

SELECT FLOOR(10E-1) // returns 1
SELECT FLOOR('-14E-4') // returns -1
SELECT FLOOR('-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 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