SQL SERVER – Puzzle – Error While Converting Money to Decimal

Earlier I had posted quick puzzle about Converting Money and I had received a wonderful response to the same. Today we will go over the solution.

The puzzle was posted here: SQL SERVER – Puzzle – Challenge – Error While Converting Money to Decimal

Run following code in SSMS:

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

The above code will give following error:

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

Why and what is the solution?

The solution is as follows:

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

There were more than 20 valid answers. Here is the reason.

Decimal data type is defined as Decimal (Precision, Scale), in other words Decimal (Total digits, Digits after decimal point).. Precision includes Scale. So Decimal (5,2) actually means, we can have 3 digits before decimal and 2 digits after decimal. To accommodate 12345.67 one need higher precision. The correct answer would be DECIMAL (7,2) as it can hold all the seven digits.

Here is the list of the experts who have got the correct answer and I encourage all of you to read the same over here.

• Fbncs
• Piyush Srivastava
• Dheeraj
• Abhishek
• Anil Gurjar
• Keval Patel
• Rajan Patel
• Himanshu Patel
• Anurodh Srivastava
• aasim abdullah
• Paulo R. Pereira
• Chintak Chhapia
• Scott Humphrey
• Alok Chandra Shahi
• Imran Mohammed
• SHIVSHANKER

The very first answer was provided by Fbncs and Dheeraj had a fascinating comment.

Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter.