Arithmetic overflow error converting varchar to data type numeric.

Question

Post reply

Arithmetic overflow error converting varchar to data type numeric.

scottgr21

Grasshopper

Points: 19
More actions
October 13, 2014 at 7:47 am

#145427

It is very strange I get this error when I run the query but then it goes away after a few minutes? What would cause it to go away but yet shows up on the running of the query?
Thanks,
Scott

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Absolutely no idea without you posting the query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Talada SSChampion Points: 11954 More actions · Answer 2

Converting varchar to numeric is inherently unsafe unless the data is app generated and not user entered. You will have random errors depending on which range of rows your query is hitting. Select all rows, converting that column to numeric and you'll find your error.

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Yeah I'll echo what Bill said. Basically, you can have a query which runs perfectly fine some times, and other times it will return an error - *even if* the record set that you get back doesn't contain any problems.

Example:

You have a table with two columns, one of which is a VARCHAR data type. In the column, you have the following records

2014-01-01 15

2014-01-02 20

2014-01-03 33

2014-02-02 14

2014-02-05 23

2014-02-15 ABC

2014-11-15 32

2014-12-15 12

Suppose you wrote the following query :

SELECT CAST(Column AS DECIMAL(18, 2))

FROM Table

WHERE Date < '2014-02-01'

Sometimes that will work, and give you back the records you want. But other times, even though the record from 2014-02-15 is not being selected, it will still try to convert it, and cause an error.

If you really must have a VARCHAR column which can contain numbers and letters, and you need to convert it, what you should do is first select those records into a temporary table, which will ensure that there are no non-numeric characters, and then run your query on the temporary table