How to Precisely Round Numbers to Two Decimal Places in SQL?

How to Round Values to Two Decimal Places with Precision in SQL

When dealing with numerical calculations in SQL, precision is often crucial. Rounding off values to a specific number of decimal places ensures data accuracy and readability.

Problem:

You need to convert minutes into hours, rounded off to two decimal places. However, using the round() function alone may result in additional decimal places, such as 10.5000000 instead of the desired 10.50.

Solution:

To achieve the desired precision, use the cast() function:

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

Explanation:

• The round() function rounds the result of minutes/60.0 to two decimal places.
• The cast() function then converts the rounded value to a numeric data type with a precision of 36 (to handle potential rounding errors) and a scale of 2 (for two decimal places).
• This ensures that the output is rounded to two decimal places with no additional decimals displayed.

Example:

If the minutes variable contains a value of 630:

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

Returns:

10.500000    10.50

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