I recently was looking at some old code that generates SQL statements. Part of this code built a string that rounded a double value to 2 decimal places in SQL - it had been working successfully for many years, but all of a sudden one instance was failing. Here is an example of a successful string:
SELECT ROUND(1.14172780803793, 2) as RoundedNum
While this is an example of a failing string , resulting in the familiar error message "Arithmetic overflow error converting expression to data type numeric.":
SELECT ROUND(0.997721736173984, 2) as RoundedNum
If you read the MSDN documentation on the T-SQL ROUND function, you'll see that the output is limited by the precision and scale of the data type that is passed as the expression argument. Since the SQL statements are being built dynamically we don't explicitly define a data type for the value being passed to the function. To see what data type is being interpreted we could execute something like the following, which will tell us that the data type for our failing value is numeric(15,15):
What this means is that the number has a precision of 15 (15 digits total), and a scale that is also 15 (all 15 digits are to the right of the decimal). When this value attempts to round to 2 decimal places, it will become a value of 1.000000000000000 which is no longer the same data type (it now has a type of numeric(16,15) because there is a digit to the left of the decimal).
There are many different ways to solve this, some are more limited than others. Ideally this value would just get rounded in the code (C#, VB.NET, whatever language you may be using likely has a ROUND function). If for whatever reason this needs to be done in SQL, it would be best to parameterize and define the data type of this value so that you know what you're working with. Obviously we wouldn't be able to give this a data type of numeric(15,15) because we might have to round to 1 again. In this particular scenario, numeric(16,15) would work fine but if we ever ended up with a number like 9.997721736173984 we would encounter the same error while the value attempts to round to 10 (another digit added to the left of the decimal). Another option would be to make the value a float, which gives the greatest flexibility, but has the trade-off of potential loss of accuracy. Since I know we aren't working with very large numbers, and we are going to be rounding to 2 decimal places, float will work just fine.
My problem is then solved by changing the SQL string to the following:
SELECT ROUND(CAST(0.997721736173984 AS FLOAT), 2)
Note that this will cut off the value returned to be 1 (or 1.14 for the successful string above), so if you want to always display two decimal places you'll need to include additional formatting in your code.
About the Author:
TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.