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

Earlier I wrote SQL SERVER – Challenge – Puzzle – Usage of FAST Hint and I did receive some good comments. Here is another question to tease your mind. Run following script and you will see that it will thrown an error.
`DECLARE @mymoney MONEY; SET @mymoney = 12345.67; SELECT CAST(@mymoney AS DECIMAL(5,2)) MoneyInt; GO `

The datatype of money is also visually look similar to the decimal, why it would throw following error:

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

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

#### Related Posts

• fbncs
November 27, 2010 7:03 am

you must do :

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

you have 7 digits in @mymoney (including decimals)

• vishalgajjar1
November 27, 2010 9:29 am

Indeed!!!

• sandman
November 27, 2010 11:53 am

I agree with fbncs.

• Daniel Abbatt
November 28, 2010 3:52 am

Exactly. It’s not 5 number followed by 2 numbers. 5, 2 means that it’s 5 digits in total with 2 after the decimal point. To represent the number you show, it needs a total of 7 with 2 after the decimal place and 5 before.

• fbncs
December 5, 2010 11:46 pm

shure, but don’t forget… if you have 4 decimals in @mymoney, you’ll lost information doing this convertion. The most correclty to do in that case is DECIMAL(9,4).

• Soumya Sunu
December 7, 2012 5:51 pm

Exactly, first digit shows the total length

• howsya
November 27, 2010 8:09 am

The money data type goes out to 4 places after the decimal.

Your data type @mymoney is set to round to the nearest penny (2 places). SQL cannot handle the conversion and will throw out the overflow error.

Try SELECT CAST(@mymoney AS DECIMAL(5,4)) MoneyInt;

• Ross
October 29, 2013 5:02 pm

As with the original post, this is wrong, DECIMAL(5,4) would hold for example, 1.2345, the first number is the total length, the second number is the decimal points

• Piyush Srivastava
November 27, 2010 8:37 am

You need to increase the size of precision. You need to make it 7 or above.

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

O/P-> 12345.67

Thanks & Regards,
Piyush srivastava
Mumbai

• Dheeraj
November 27, 2010 9:50 am

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

The target data type is unable to accommodate needful value. We will need to give enough room by expanding the Decimal value and precision.

• Abhishek
November 27, 2010 9:58 am

In Your question while you converting money to decimal you are using decimal(5,2) that means 2 is precision in total number 5 digit including precision that means only 3 digit and your money value is greater then hundred

• Dheeraj
November 27, 2010 10:04 am

Another approach (NOT RECOMMENDED)

SET ARITHABORT OFF

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

The above method is the way to silently fail the conversions, in which case the CAST function will return a NULL Value. Notice the SET statement that explicitly sql server to ignore arithmetic overflows.

Having said that, this approach is not a recommended practice and should not be used any environment (DEV,QA,STAG,PROD).

• Anil Gurjar
November 27, 2010 11:32 am

you must do :

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

you have 7 digits in @mymoney (including decimals)

• Anil Gurjar
November 27, 2010 11:34 am

you must do :

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

you have 7 digits in @mymoney (including decimals) so you put abc=7 or greater than 7

• Keval Patel
November 27, 2010 11:36 am

You need to specify

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

Because…

Here first part specifies total number of decimals. i.e. number of characters before “Period” (.) + number of characters after “Period” (.)

5+2 = 7

Second part sepcifies how many characters should be after “Period” (.)

• Rajan Patel
November 27, 2010 12:22 pm

DECIMAL(5,2) has 5 precision and 2 scale.

Precision is the total number of digits in a number.

Scale is the number of digits to the right of the decimal point in a number.

For Example the number 8714.235 has a

precision – 7

scale – 3

• Himanshu Patel
November 27, 2010 12:37 pm

Hi pinal

as per the bol.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12345.67 is converted into a numeric value with a precision of 7 and a scale of 2.

section : Converting decimal and numeric Data

• Anurodh Srivastava
November 27, 2010 1:54 pm

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

Now it will not through any error.
Since @mymoney variable has 5 digits before decimal and 2 digits after decimal. So we have to put 7 insead of 5 in precision of DECIMAl Datatype, because 5 + 2 = 7 means total no of digits that DECIMAL datatype may contain before decimal and after decimal place.
For understanding, you can also see it like this DECIMAL((7-2),2).

Have a look the detail given below for more clarity-
DECIMAL(p,s)
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point.

• aasim abdullah
November 27, 2010 3:01 pm

Query from “fbncs” is correct because decimal data type takes 2 bytes of overhead to store the offset to the value. Which money don’t. Thats why converting from money to decimal need 2 bytes extra otherwise enjoy arithmetic overflow error ;)

• Paulo R. Pereira
November 27, 2010 4:02 pm

Hi Pinal,

The overflow problem is because the length of DECIMAL(5,2) is small to support this value.

In this case, the value 12345.67 has length (precision) of 7 digits, when 2 digits are dedicated to “.67” (scale), then you can convert this number to a DECIMAL(7,2) and not to a DECIMAL(5,2).

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

But you can lost the 2 digits dedicated to “.67”, if you use a small decimal with a compatible precision without scale:

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

• Chintak Chhapia
November 27, 2010 5:17 pm

Yes I agree with Fbncs.

Just adding description for whay it’s filing when we rty to convert it to Decimal(5,2)

In decimal decalaration, DECIMAL(P,S), P (precision) specifies the number of digits on both side of decimal point. 12345.67 has 7 digits ( of 2 digits are of scale), so we need decimal(5,2) for storing this number.

Even if we tries to convert the number to DECIMAL(7,3), we get same error as we specify scale as 3 and so, we can have only 4 digits on left side of decimal point.

-Chintak

• Scott Humphrey
November 27, 2010 5:36 pm

You need to define the decimal larger to cast money to

• Alok Chandra Shahi
November 27, 2010 8:03 pm

You are taking 5 in place of 7.Because here length of This Money type variable will be fit in 5 digit before decimal point so you should take at least 5 digit in decimal datatype. digit after decimal will be round off.

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

• Imran Mohammed
November 27, 2010 10:45 pm

Decimal data type is defined as Decimal (Precision, Scale).

Precision includes Scale.

So Decimal (5,2) actually means, we can have 3 digits before decimal and 2 digits after decimal.

From above example, (3+2) digits is called as precision and 2 digits after decimal is callled as scale.

In your example, you have 5 digits before decimal and 2 digits after decimal, therefore the correct decimal should be Decimal (7,2).

~ IM.

• SHIVSHANKER
November 29, 2010 6:20 pm

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

SIR HERE THE DECIMAL VALUE ACCEPT

DECIMAL(TOTAL DIGIT,DECIMAL POINT AFTER DIGIT)

SO USE

SELECT CAST(@mymoney AS DECIMAL(7,2)) MoneyInt;

IT WILL RUN

• VINAY
December 10, 2012 5:58 pm

Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

p (precision) :
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.
s (scale) :
The maximum number of decimal digits that can be stored to the right of the decimal point.