BROUND

Round a numeric value using banker's rounding (round half to even).

Category: mathReturns: DOUBLEDialect: Standard

Syntax

BROUND(expr [, d])

Description

## Overview Returns the input rounded to the specified number of decimal places using banker's rounding (round half to even). When the digit being dropped is exactly 5, the remaining digit is rounded to the nearest even value: 2.5 rounds to 2, 3.5 rounds to 4, and 1.25 rounds to 1.2. This convention eliminates the systematic upward bias that half-away-from-zero rounding introduces in large aggregates. BROUND is the preferred rounding mode for financial reporting, statistical summaries, and any workload where an unbiased expectation of the sum of many rounded values matters. It matches IEEE 754 default rounding and the behavior of Python's built-in round() on floats. ## Behavior - Accepts any numeric type for the value. The return type follows the input type's rules. - Returns NULL if either argument is NULL. - Uses round-half-to-even: 2.5 -> 2, 3.5 -> 4, 2.45 -> 2.4, 2.35 -> 2.4. - Negative scale rounds to the left of the decimal point. - For non-halfway values, BROUND produces the same result as ROUND. ## Numeric precision - For DECIMAL inputs, rounding is exact. - For DOUBLE inputs, the same binary-representation issues that affect ROUND apply. BROUND(0.125, 2) may not return exactly 0.12 if 0.125 is stored as a value other than the mathematical 0.125. - Pair BROUND with DECIMAL storage for currency amounts to guarantee the expected behavior. ## Compatibility - BROUND implements the banker's rounding mode (round half to even) also known as IEEE 754 default rounding. - Matches the behavior of Python's round() on floats and of common statistical libraries.

Parameters

Name	Type	Description
`expr`		Specifies the numeric value to round. Accepts any numeric type; the return type generally matches the input type.
`d`		Specifies the number of decimal places. Use 0 for whole-number rounding. Negative values round to positions left of the decimal point.

Examples

-- Halfway value rounds to even (2 is even)
SELECT BROUND(2.5, 0);
-- Result: 2.0

-- Halfway value rounds to even (4 is even)
SELECT BROUND(3.5, 0);
-- Result: 4.0

-- Non-halfway value rounds normally
SELECT BROUND(2.6, 0);
-- Result: 3.0

-- Round to 1 decimal place with halfway at 1.25 -> 1.2 (2 is even)
SELECT BROUND(1.25, 1);
-- Result: 1.2

-- Negative halfway value rounds to even (4 is even)
SELECT BROUND(-4.5, 0);
-- Result: -4.0

-- NULL propagation
SELECT BROUND(NULL, 2);
-- Result: NULL

Pitfalls

BROUND disagrees with ROUND only at exact-halfway values. Code that assumes ROUND(2.5) = 3 will break when silently replaced with BROUND.
For DOUBLE inputs, BROUND(2.675, 2) can return 2.67 rather than 2.68 because 2.675 is not exactly representable in binary. Use DECIMAL for exact currency rounding.
BROUND is safe to sum across a large number of rows because the expected rounding error is zero, unlike ROUND which biases sums upward for positive values.
BROUND preserves DECIMAL scale. If you want an integer type, cast explicitly after rounding.
BROUND pairs with DECIMAL storage. Combining BROUND with DOUBLE storage can still produce surprising results for values whose binary representation is slightly off from the decimal intent.