Have you ever encountered an error while converting money to decimal in SQL Server? If so, you’re not alone. In this article, we will explore the concept of precision and scale in SQL Server and how it affects data conversion.

Let’s start with a puzzle. Consider the following code:

DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(5,2)) MoneyInt;

When you run this code in SQL Server Management Studio (SSMS), you will encounter the following error:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting money to data type numeric.

So, why does this error occur and what is the solution?

The error occurs because the decimal data type is defined as Decimal(Precision, Scale). Precision refers to the total number of digits that can be stored, including both digits before and after the decimal point. Scale refers to the number of digits that can be stored after the decimal point.

In our puzzle, we have specified DECIMAL(5,2), which means we can have 3 digits before the decimal point and 2 digits after the decimal point. However, the number 12345.67 requires a higher precision to be stored correctly.

The correct solution would be to use DECIMAL(7,2), which can accommodate all seven digits of the number. Here’s the updated code:

DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(7,2)) MoneyInt;

By using DECIMAL(7,2), we can successfully convert the money value to a decimal without encountering any errors.

It’s worth noting that there were more than 20 valid answers to this puzzle, and many experts provided insightful explanations. If you’re interested, you can find their answers in the comments section of the original puzzle post.

Understanding precision and scale in SQL Server is crucial when working with numeric data types. By choosing the appropriate precision and scale for your data, you can ensure accurate storage and avoid errors during conversions.

That’s all for this article. I hope you found it helpful in understanding precision and scale in SQL Server. Stay tuned for more SQL Server tips and tricks!