602SQL Documentation Index  

Rounding Numbers in SQL

The SQL language does not have a standard function for rounding numbers. However it is easy to use conversion with the CAST function for rounding.

Rounding to a Specified Decimal Digit

Use this function for rounding:

CAST(value AS NUMERIC(max_count_of_valid_digits, count_of_decimal_digits))

Where value is the value to be rounded, max_count_of_valid_digits is the maximum digits needed for the value (including decimal places) and count_of_decimal_digits is the count of decimal digits that we are rounding to.

The maximum number of digits may not exceed 19 for accuracy in numeric calculations.

Rounding to the Powers of Ten

A similar method is used for rounding to the power of ten (e.g. tens, hundreds, thousands, millions). Divide the value with the selected power, round to an integer, and then multiply by the same power:

CAST(value/1eN AS NUMERIC(max_count_of_valid_digits, 0)) * 1eN

Where N is the power.

Round to thousands (third power of ten) using the following command:

CAST(value/1e3 AS NUMERIC(19, 0)) * 1e3

You can also use the client function Round or Trunc in the SQL.