round function

Applies to: Databricks SQL Databricks Runtime

Returns the rounded expr using HALF_UP rounding mode.

Syntax

round(expr [, targetScale] )

Arguments

• expr: A numeric expression.

• targetScale: An INTEGER constant expression. If targetScale is omitted the default is 0 (whole number).

  In Databricks SQL and Databricks Runtime 12.2 LTS and above: If targetscale is negative rounding is performed to positive powers of 10.

Returns

If expr is DECIMAL the result is DECIMAL with a scale that is the smaller of expr scale and targetScale. For all other numeric types, the result type matches expr.

In HALF_UP rounding, the digit 5 is rounded up.

For HALF_EVEN rounding use the bround function.

Avertisment

In Databricks Runtime 12.2 LTS and below, and in Databricks Runtime if spark.sql.ansi.enabled is false, an overflow does not cause an error but “wraps” the result instead.

Examples

SQL

> SELECT bround(2.5, 0), round(2.5, 0);
 2    3

> SELECT bround(3.5, 0), round(3.5, 0);
 4    4

> SELECT bround(2.6, 0), round(2.6, 0);
 3    3

> SELECT bround(2.25, 1), round(2.25, 1);;
 1.2  2.3

> SELECT bround(13.5, -1), round(13.5, -1);
 10   10

Related functions

• floor function
• ceiling function
• ceil function
• bround function