sqlsql-servert-sqlrounding

Truncate (not round) decimal places in SQL Server


I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example:

declare @value decimal(18,2)

set @value = 123.456

This will automatically round @value to be 123.46, which is good in most cases. However, for this project, I don't need that. Is there a simple way to truncate the decimals I don't need? I know I can use the left() function and convert back to a decimal. Are there any other ways?


Solution

  • You will need to provide 3 numbers to the ROUND function.

    1. number Required. The number to be rounded
    2. decimals Required. The number of decimal places to round number to operation
    3. Optional. If 0, it rounds the result to the number of decimal. If another value than 0, it truncates the result to the number of decimals. Default value is 0

    Example:

       select round(123.456, 2, 1)
    

    Works in:

    Additional Info: https://www.w3schools.com/sql/func_sqlserver_round.asp