How to Truncate, Not Round, Decimal Places in SQL Server?

SQL Server: Precision in Decimal Handling – Truncation vs. Rounding

Accurate decimal calculations in SQL Server often require choosing between truncation and rounding. This guide details the key differences and demonstrates how to truncate decimal values without rounding.

Illustrative Example:

<code class="language-sql">DECLARE @value DECIMAL(18,2);
SET @value = 123.456;</code>

SQL Server automatically rounds @value to 123.46. While suitable for many applications, scenarios exist where rounding is undesirable, necessitating truncation.

Effective Truncation Techniques

1. Leveraging the ROUND Function:

The ROUND function offers a subtle yet powerful feature: a third parameter controlling truncation.

<code class="language-sql">ROUND(123.456, 2, 1);</code>

A non-zero third parameter instructs ROUND to truncate rather than round.

2. Utilizing CAST and CONVERT:

Casting or converting the decimal to an integer type directly truncates the decimal portion:

<code class="language-sql">CAST(@value AS INT);
CONVERT(INT, @value);</code>

3. String Manipulation with SUBSTRING or LEFT:

Extract the desired portion using SUBSTRING or LEFT, then convert back to a decimal:

<code class="language-sql">SUBSTRING(@value, 1, 4);
LEFT(@value, 4);</code>

4. Employing the FLOOR Function:

The FLOOR function, combined with multiplication and division, provides another truncation method:

<code class="language-sql">FLOOR(@value * 100) / 100;</code>

This effectively truncates to two decimal places. Remember to adjust the multiplier and divisor for different precision levels.

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