Truncate (not round) decimal places in SQL Server

How to truncate with ROUND

SQL Server’s ROUND function could also get this done. Its third parameter controls the execution:

-- Let's be direct, ROUND doesn't round when third param is 1
SELECT ROUND(YourColumn, 2, 1) AS TruncatedValue FROM YourTable


When the third parameter is 1, ROUND pulls a switcheroo and truncates the number instead. Here, 2 reflects the number of decimal places.

Truncation beyond decimal realm

Venturing beyond decimal limits, use a negative length for whole number truncation:

-- I wish my bank account would ROUND UP like this...
SELECT ROUND(YourColumn, -2, 1) AS TruncatedWholeNumber FROM YourTable


This action does a number "haircut" to the closest whole number in the hundreds, eliminating any decimals.

Take the road less travelled: Alternatives to ROUND

Although ROUND triumphs, other methods like the unsung modulo operator (%) can perform truncation:

-- Go home, ROUND! We got % now!
SELECT YourColumn - (YourColumn % 1) AS TruncatedValue FROM YourTable


This formula obtains the modulo of YourColumn with 1 and subtracts it from YourColumn, effectively truncating the decimals.

Precision tuning post-truncation

Following truncation, ensure proper scale and precision by casting the result:

-- We aren't taking any chances with precision
SELECT CAST((YourColumn - (YourColumn % 1)) AS DECIMAL(10,2)) AS TruncatedValue FROM YourTable


This shaves the number down to the desired decimal format.

Not on my watch: Avoid missteps

Pro tip: Steer clear of string functions like LEFT to truncate numerics, as it necessitates converting back to a numeric type, leading to potential hiccups or surprise scenarios.

SQL Truncation Uni: Applications across SQL databases

Noteworthy, these truncation practices extend to various SQL Server versions (2008 and beyond) including Azure SQL Database and SQL Data Warehouses, making it a popular tool across numerous setups.

Data type chooses you, you don't choose data type

Pick your data type wisely. DECIMAL types tend to win battles for fixed precision and scale, while types like FLOAT and REAL may fall flat to rounding issues due to their approximate nature.

Best practices 🔧

Edge cases could construe truncation as conflicting with business logic rules or statistical operations. Sticking to the set precision in your data contracts is key for data consistency.