 |
|
|
 | Expressions with Date and Time |  | Expressions |
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.
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.
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.
| Expressions with Date and Time | | Expressions |