How to Round Numerical Values to Two Decimal Places in SQL?

Rounding Numerical Values to Two Decimal Places in SQL

Rounding off numerical values to specific decimal places is often necessary when displaying data in a consistent and precise manner. In SQL, this task can be accomplished using various functions and techniques.

Example Scenario

Consider the task of converting minutes to hours, rounded to two decimal places, while ensuring that only two digits are displayed after the decimal point. For instance, converting 650 minutes to hours should result in 10.83. However, using the following SQL query:

Select round(Minutes/60.0,2) from ....

yields a result of 10.5000000 for 630 minutes. The goal is to obtain a rounded value of 10.50 instead.

Solution

To achieve the desired rounding with precise decimal point control, you can employ the following technique:

select
    round(630/60.0,2),
    cast(round(630/60.0,2) as numeric(36,2))

The round() function alone does not provide control over the number of decimal places displayed. However, by casting the rounded value to a numeric type with an appropriate precision (e.g., numeric(36,2)), you can limit the output to two decimal places.

Explanation

• The round() function rounds the expression 630/60.0 to two decimal places, resulting in 10.500000.
• The cast() function converts the rounded value to a numeric data type with a precision of 36 and a scale of 2. This restricts the output to two decimal places, truncating any additional digits.

Expected Output

Executing the revised SQL query returns the following results for the given examples:

10.500000    10.50

This accurately rounds the value to two decimal places, displaying only 10.50 in the second column.

The above is the detailed content of How to Round Numerical Values to Two Decimal Places in SQL?. For more information, please follow other related articles on the PHP Chinese website!