Pages

Wednesday, December 1, 2010

Error Converting Data Type Money to Decimal

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.
 
  
Solution:
 
DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(7,2)) MoneyInt;

 
Explanation: 
you have 7 digits in @mymoney (including decimals) 
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.

1 comment:

Hales said...

I love the way you write and share your niche! Very interesting and different! Keep it coming! convert money