SQL SERVER – Solution – Puzzle – Challenge – Error While Converting Money to Decimal

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)

3 thoughts on “SQL SERVER – Solution – Puzzle – Challenge – Error While Converting Money to Decimal

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s