# floor function

Applies to: Databricks SQL 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