Error : Arithmetic overflow error converting numeric to data type varchar.
Getting error at this line why and what should be changed?
CONVERT(VARCHAR(8),CONVERT(DECIMAL(8,4),((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100))
11
votes
can anyone help me to identify what I need to change :( please
- Neo
Please post better info next time. Normally you get a line number with the error. Also it's nice to see you attempted to resolve it yourself.
- JNK
If you want people to spend time helping you you could put a little more effort into the question. What have you tried so far? Where does it fall over? What are the input values. I suggest you do some debugging, I would look at CONVERT(DECIMAL(8,4) for starters.
- Simon
2 Answers
35
votes
Here's at least one issue:
CONVERT(VARCHAR(8),CONVERT(DECIMAL(8,4))
The Decimal(8,4) indicates 8 numeric digits, 4 to the right of the decimal. This does NOT account for the actual decimal character, so you potentially have a value like:
1234.5678
which is a valid Decimal(8,4) but won't fit in a varchar(8).
1
votes
I know this is an old post but I just wanted to say thanks. I was having this exact problem and what was most annoying was that it gave the error when selecting from a VIEW , but did not give the error when I used the select statement from the VIEW and pasted it and inserted it into a TEMP TABLE!!
example:
select * from dvView --worked
select * from dvView where product = '5' --Broke!
--BUT
select * from #Temp_table_dvView --worked!
select * from #Temp_table_dvView where product = '5' --worked!
in the end, I had to changed a part in the view from
select cast(productNumber as nvarchar(1), etc...
to
select cast(productNumber as nvarchar(2), etc...
and it worked.
but weird that the error I got was
Arithmetic overflow error converting numeric to data type varchar.
instead of the one that reads
Data would be truncated
or whatever...
food for thought.
