Controlling Decimal Precision: Truncation vs. Rounding in SQL Server

When working with decimal numbers in SQL Server, you often need to control the number of digits displayed after the decimal point. While rounding is a common operation, sometimes you need to truncate the decimal portion – meaning simply remove the extra digits without adjusting the value based on standard rounding rules. This tutorial explains how to achieve both rounding and truncation in SQL Server.

Understanding Rounding and Truncation

Rounding: Adjusts a number to the nearest value based on a specified precision. For instance, rounding 123.456 to two decimal places results in 123.46.
Truncation: Simply removes the digits beyond the specified decimal places, discarding them without considering the next digit. Truncating 123.456 to two decimal places results in 123.45.

Using the `ROUND()` Function

The ROUND() function in SQL Server is the primary tool for both rounding and truncation. Its syntax is as follows:

ROUND ( numeric_expression , length [ , function ] )

numeric_expression: The number you want to modify.
length: The number of decimal places to which you want to round or truncate. A positive value specifies decimal places; a negative value rounds to the left of the decimal point.
function: This optional parameter determines whether to round or truncate.
- 0 (or omitted): Rounds the number to the specified length.
- Any other value (e.g., 1, 2): Truncates the number to the specified length.

Examples:

-- Rounding to two decimal places
SELECT ROUND(123.456, 2);  -- Output: 123.46

-- Truncating to two decimal places
SELECT ROUND(123.456, 2, 1); -- Output: 123.45

As you can see, including the third parameter (set to a non-zero value) instructs SQL Server to truncate instead of round.

Casting for Clean Output

After truncating, you might notice trailing zeros (e.g., 123.450). If you need a cleaner output without these trailing zeros, you can cast the result to the desired decimal type:

SELECT CAST(ROUND(123.456, 2, 1) AS DECIMAL(18, 2)); -- Output: 123.45

This casting operation removes the trailing zeros, providing a clean and concise result. The DECIMAL(18,2) specifies a total of 18 digits, with 2 after the decimal point. Adjust these values as needed for your specific data requirements.

Alternative Truncation Method (Less Recommended)

While the ROUND function with the truncation parameter is the recommended approach, you can also achieve truncation using arithmetic operations, although this is generally less readable and maintainable.

SELECT 123.456 - (123.456 % 0.01); -- Output: 123.45

This approach calculates the remainder when dividing by 0.01 (which represents two decimal places) and subtracts it from the original number, effectively truncating it. While functional, it’s best to prioritize clarity and maintainability using the ROUND function.

Summary

Controlling decimal precision is crucial for accurate data representation and reporting. The ROUND function in SQL Server provides a flexible solution for both rounding and truncation. By utilizing the optional function parameter and casting to the appropriate decimal type, you can achieve the desired level of precision and output format for your data. Remember to prioritize clarity and maintainability in your code to ensure its long-term usability.