bround function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the rounded expr using HALF_EVEN rounding mode.

Syntax 

bround(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 max(targetScale, 0). For all other numeric types the result type matches expr.

In HALF_EVEN rounding, also known as Gaussian or banker’s rounding, the digit 5 is rounded towards an even digit.

For HALF_UP rounding use the round 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