T-SQL

Removing decimal places from number without rounding

ROUND() is well known function in SQL Server. Most of us knows only that two parameters can be user like ROUND(99,95, 2). This will do standard mathematical up/down rounding of number in first parameter for number of decimal places from second parameter.

But there is also an option to use third parameter called function specified in BOL as:

Function is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Let’s observer this simple example:

Using third parameter with different value then 0 we have simply trimmed decimal places which can be really handy in case of some specific need for reporting when there is requirement to report numbers without decimal place but also have a proper value at the total row.

Let me mention one important thing we should remember using ROUND()  function.

Consider following example:

Are you surprised about this error?

Explanation is easy: value 9.9 is handled in this case as to be DECIMAL(2,1). Rounding it to 0 decimal places means that the new value will be 10 and this doesn’t fit into the original DECIMAL(2,1) data type.

This can be easy fixed like this:

I saw lot of production disasters caused by this simple peace of code during my career.

Leave a Reply

Your email address will not be published. Required fields are marked *