ROUND

​

Syntax

​

Scalar

round(value[, decimal_places])

​

Arguments

​

Returns

• Type: FLOAT for FLOAT input; DOUBLE for other nondecimal input; DECIMAL with adjusted precision and scale for decimal input
• Possible values: The number rounded to decimal_places digits.
• NULL behavior: Returns NULL when value or an explicitly provided decimal_places is NULL.

​

Properties

• Approximation bound: No fixed bound is declared for floating-point rounding.

​

Usage notes

• The optional decimal_places argument defaults to zero and need not be a constant.
• For a decimal value and literal nonnegative decimal_places, the output scale is the smaller of the input scale and decimal_places. Negative positions use output scale zero.
• For an integer-scale decimal rounded at a negative position, precision can increase by one, up to the decimal type maximum.
• For a decimal value and nonliteral decimal_places, the result retains its input scale and may gain one precision digit to accommodate a carry.
• Floating-point inputs multiply by 10^decimal_places, round and divide back, so binary representation and intermediate overflow/underflow can affect extreme cases.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Normal values and NULL behavior

SELECT round(CAST(2.5 AS DOUBLE)) AS positive_half, round(CAST(-2.5 AS DOUBLE)) AS negative_half, round(CAST(123.45 AS DOUBLE), -1) AS tens, round(CAST(NULL AS DOUBLE)) AS null_value;

​

Decimal places and trailing digits

WITH sample AS (
  SELECT id, CASE WHEN id = 0 THEN CAST(12.345 AS DECIMAL(8,3)) ELSE CAST(12.344 AS DECIMAL(8,3)) END AS amount
  FROM range(0, 2)
)
SELECT amount, round(amount, 2) AS rounded FROM sample ORDER BY id;

​

Limitations

• Decimal precision overflow and unsupported out-of-range decimal_places can raise an execution error.