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)
Filed under: Pinal Dave, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
