Snowflake
Number Rounding

Snowflake ROUND: Precision Rounding of Numbers

The ROUND function in Snowflake modifies a numerical value to a specified number of decimal places. It’s useful when you need to adjust the precision of numbers, especially for display or when performing calculations that require rounding.

Syntax

The basic syntax of the ROUND function is:


_10
ROUND(input_expr, scale_expr, rounding_mode)

where:

  • input_expr: The number or expression you want to round.
  • scale_expr: Specifies how many decimal places to keep. If you omit this, it defaults to zero, rounding to the nearest whole number.
  • rounding_mode: Optional. Dictates how the function rounds half-values; can be HALF_AWAY_FROM_ZERO (default) or HALF_TO_EVEN.

You can also use named arguments for clarity:


_10
ROUND(EXPR => input_expr, SCALE => scale_expr, ROUNDING_MODE => rounding_mode)

How It Works

  • Default Behavior: Without a rounding mode specified, Snowflake rounds half-values away from zero. For example, 0.5 rounds to 1, and -0.5 rounds to -1.
  • Precision: The scale can be positive or negative:
    • Positive scale: Rounds to the specified number of decimal places.
    • Negative scale: Rounds to the left of the decimal, useful for rounding to the nearest 10, 100, etc.

Examples

Basic Rounding

Round to the nearest whole number:


_10
SELECT ROUND(123.456); -- Returns 123
_10
SELECT ROUND(-123.456); -- Returns -123

Specifying Decimal Places

Round to two decimal places:


_10
SELECT ROUND(123.456, 2); -- Returns 123.46

Round to the nearest hundred (using a negative scale):


_10
SELECT ROUND(123.456, -2); -- Returns 100

Using Rounding Modes

Round 2.5 to the nearest even number:


_10
SELECT ROUND(2.5, 0, 'HALF_TO_EVEN'); -- Returns 2
_10
SELECT ROUND(2.5, 0); -- Returns 3 using DEFAULT: HALF_AWAY_FROM_ZERO

Usage Tips

  • Consistency: Ensure the data type of your input matches your needs. ROUND can handle both FLOAT and NUMBER types, but the behavior may differ slightly due to how floating points are represented.
  • Avoid Mixing Arguments: Do not mix named and positional arguments in the same function call to avoid confusion and errors.
  • Data Type Limits: Be cautious with the rounding_mode; it’s not compatible with floating point numbers. If you need specific rounding rules for floats, consider casting to NUMBER first.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started