SQL Server Journey with SQL Authority

Earlier I had posted quick puzzle and I had received 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


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?

Solution is as following:

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 are the list of the experts who have got correct answer and I encourage all of you to read the same over hear.

• 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 very interesting comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)