SQL Arithmetic overflow error converting expression to data type nvarchar

Question

Error: Arithmetic overflow error converting expression to data type nvarchar.

UPDATE [dbo].ForecastAccuracyKeyAccounts
SET ThreeMonthPercent = ((Actual - ThreeMonthForecast) / Actual) * 100, SixMonthPercent = ((Actual - SixMonthForecast) / Actual) * 100, 
NineMonthPercent = ((Actual - NineMonthForecast) / Actual) * 100
WHERE Actual != 0

Since, I am dividing by Actual, I want to ensure that Actual is not equal to zero. But when I add the statement in the WHERE CLAUSE I cannot get rid of the error.

what data types are your columns? It looks like there is something funky going on with type conversions — Brad
– Brad, Commented Feb 10, 2014 at 20:00
Actual float; ThreeMonthPercent nvarchar(7); ThreeMonthForecast float; This worked fine before I added the Where Clause. The only error I would get is when the Actual column was 0. — ahsatan
– ahsatan, Commented Feb 10, 2014 at 20:04
Can you show the values that are causing the expression to fail? — Gordon Linoff
– Gordon Linoff, Commented Feb 10, 2014 at 20:17
I'm not entirely sure ... but does SQL Server guarantee the order of processing (WHERE before SET)? If not, it could evaluate the SET statements first before applying the WHERE clause. [In which case you'll need to test for Actual=0 somehow in your SET statement, e.g. ... /NULLIF(Actual,0)] — richaux
– richaux, Commented Feb 10, 2014 at 20:38

Hector Sanchez · Accepted Answer · 2014-02-10 21:15:36Z

1

Maybe try ISNULL

UPDATE [dbo].ForecastAccuracyKeyAccounts

    SET ThreeMonthPercent = ((Actual - ISNULL(ThreeMonthForecast,0)) / Actual) * 100,

    SixMonthPercent = ((Actual - ISNULL(SixMonthForecast,0)) / Actual) * 100, 

    NineMonthPercent = ((Actual - ISNULL(NineMonthForecast,0)) / Actual) * 100

    WHERE Actual != 0

edited Feb 10, 2014 at 21:15

Hector Sanchez

2,3274 gold badges26 silver badges40 bronze badges

answered Feb 10, 2014 at 20:41

Rick S

6,5966 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rick S Over a year ago

Sorry about the formatting, i'm posting from my mobile phone. You have a NULL value for ThreeMonthForecast. That is the problem. Using IsNull should fix it.

ahsatan Over a year ago

This does not work, I still get the overflow error. Thank you for your help though! It's tough typing from a cellphone!

Rick S Over a year ago

Can you increase the size of your nvarchar(7) and see if that helps.

ahsatan Over a year ago

I support change the data type as a whole? This is a percentage, so maybe change it to decimal?

Rick S Over a year ago

Change your ThreeMonthPercent, SixmonthPercent and NineMonthPercent to float and see if that helps.

|

Collectives™ on Stack Overflow

SQL Arithmetic overflow error converting expression to data type nvarchar

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related