floor function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.0 and above

Returns the largest number not bigger than expr rounded down to targetScale digits relative to the decimal point.

Syntax 

floor(expr [, targetScale])

Arguments

  • expr: An expression that evaluates to a numeric.

  • targetScale: An optional INTEGER literal greater than -38 specifying by how many digits after the decimal points to round down.

Returns

If no targetScale is given:

  • If expr is DECIMAL(p, s), returns DECIMAL(p - s + 1, 0).

  • For all other cases, returns a BIGINT.

If targetScale is specified and expr is a:

  • TINYINT

    Returns a DECIMAL(p, 0) with p = max(3, -targetScale + 1).

  • SMALLINT

    Returns a DECIMAL(p, 0) with p = max(5, -targetScale + 1).

  • INTEGER

    Returns a DECIMAL(p, 0) with p = max(10, -targetScale + 1)).

  • BIGINT

    Returns a DECIMAL(p, 0) with p = max(20, -targetScale + 1)).

  • FLOAT

    Returns a DECIMAL(p, s) with p = max(14, -targetScale + 1)) and s = min(7, max(0, targetScale))

  • DOUBLE

    Returns a DECIMAL(p, s) with p = max(30, -targetScale + 1)) and s = min(15, max(0, targetScale))

  • DECIMAL(p_in, s_in)

    Returns a DECIMAL(p, s) with p = max(p_in - s_in + 1, -targetScale + 1)) and s = min(s_in, max(0, targetScale))

If targetScale is negative the rounding occurs to -targetScale digits to the left of the decimal point.

The default targetScale is 0, which rounds down to the next smaller integral number.

Examples 

> SELECT floor(-0.1);
 -1

> SELECT floor(5);
 5

> SELECT floor(3345.1, -2);
 3300

> SELECT floor(-12.345, 1);
 -12.4