If the underlying data types and rounding functions are not understood, rounding can be misconstrued. The rounded value varies depending on the data type (integer, float, decimal, etc.). Furthermore, the numbers can differ depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation. As a result, it's critical to determine the user's rounding requirements before translating them into the proper T-SQL statement.
Let's start with a definition for mathematical functions:
ROUND - Rounds a positive or negative value to a specified length, accepting one of three values:
To round the value
Numbers can be positive or negative.
This data type can be int (small, medium, large), decimal, numeric, money, or smallmoney.
When rounding, be precise.
On the right side of the decimal point, a positive number rounds.
On the left side of the decimal point, a negative number rounds.
When this value is not 0 or not included, the value is truncated to round.
CEILING - Evaluates the right-hand decimal value and returns the smallest integer greater than or equal to the specified numeric expression, accepting only one value:
To round the value
FLOOR - Evaluates the right-hand decimal value and returns the largest integer less than or equal to the specified numeric expression, accepting only one value:
To round the value
