Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Arithmetic overflow error converting expression to data type money.

Status
Not open for further replies.

luvmy2Js

IS-IT--Management
Sep 1, 2010
43
US
What am I doing wrong in the below statement:

CASE
WHEN NOT CAST(CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) as MONEY) > 0 THEN NULL
WHEN NOT c.Name IS NULL THEN (a.[PP salary-Comm] * CAST(y.[Annual Conversion] as money))
ELSE (CAST(CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) as MONEY) * CAST(y.[Annual Conversion] as MONEY))
END,


Thanks a million!!
 
Sounds like a data issue rather. Can you check that the values you are trying to convert are numeric and fall within the range of the datatype (MONEY -922,337,203,685,477.5808 to 922,337,203,685,477.5807).


MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
 
Yes they do fall within the data type of money, however I did notice the field is in the database a "float"? Could that be the issue?
 
A value may well fall within the limits of both MONEY and FLOAT or REAL datatype, you should always use the smallest datatype that can hold the greater value in your data range. That been said, try and test with a small subset of your data, to see which row fails the test. Make sure that your decryption also returns the correct numeric value; you could run a simple SELECT DecryptByKey(a.[1003-Ref Salary]) FROM YourTable a and look at the values...Then try to run a SELECT CONVERT(nvarchar, DecryptByKey(a.[1003-Ref Salary])) FROM YourTable a and so on. An overflow error means the value returned does not fit in the target data type. Take it from there.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top