CODEWITHSUNDEEP
sqlsql-servert-sqltype-conversion
Jeff.Clark
Jeff.Clark

Reputation: 611

TSQL I don't understand Arithmetic Overflow Error Converting Money to Numeric

A company is changing their DB systems, and I am in charge of preparing the data to go from the source DB to the target DB. One of the fields needs is the annual salary, and is going from a money datatype to a datatype of format "99999.999999".

I assumed something like the below would work. It first checks to see if the person is hourly, then put in 0. Otherwise, use the AnnlSal column and format for 6 points of precision.

UPDATE #SalaryInfo
SET AnnSalary = (SELECT CASE WHEN SalaryorHourly = 'H' THEN CONVERT(decimal(11,6), 0)
                             ELSE CONVERT(decimal(11,6), AnnlSal) 
                        END
                 FROM <table>
                 WHERE <table>.Emp = #SalaryInfo.EmpNo

That is giving me

Arithmetic overflow error converting money to data type numeric.

I found Arithmetic overflow error converting numeric to data type numeric, but I am not understanding why I am still having issues.

Upvotes: 0

Views: 14356

Answers (2)

yeOldeDataSmythe
yeOldeDataSmythe

Reputation: 843

When you use a decimal(11,6), your results cannot exceed $99,999.999999 for an annual salary. If one of your results hits $100,000 or above (100000.000000) will throw the overflow error.

Try using a few less points of precision. Maybe decimal(11,3) which should be enough for an Annual Salary.

Upvotes: 3

Michael
Michael

Reputation: 129

Not sure if the error generated from the first or second CONVERT. Try following to track it down. You may have a null in the incoming data:

SET AnnSalary = (SELECT 
                     CASE WHEN SalaryorHourly = 'H' THEN 0
                          ELSE CONVERT(decimal(11,6), AnnlSal) 
                     END
                 FROM <table>
                 WHERE <table>.Emp = #SalaryInfo.EmpNo)

Upvotes: 0

Related Questions