round function

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

Warning

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 

> 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);;
 2.2  2.3

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