Share via

floor function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS 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.

When targetScale is specified with an integral input type, Azure Databricks raises ARITHMETIC_OVERFLOW if the result overflows the output type.

Warning

In Databricks Runtime, if spark.sql.ansi.enabled is false, an overflow does not cause an error but "wraps" the result instead.

Common error conditions

Examples

> SELECT floor(-0.1);
 -1

> SELECT floor(5);
 5

> SELECT floor(3345.1, -2);
 3300

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

-- Rounding -128 to the nearest 10 produces -130, which overflows TINYINT.
> SELECT floor(cast(-128 AS TINYINT), -1);
  Error: ARITHMETIC_OVERFLOW
  • Last updated on