Operand data type varchar is invalid for subtract operator. in SQL Server

Question

I have a query like this :

Update T_Assets set F_Asset_CurrentValue=(F_Asset_CurrentValue - '99.58')
Where F_Asset_Code='ITSRDL00001'

but I'm getting an error:

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for subtract operator

Remove the quotes around 99.58, it's a numeric literal, not a string literal. — jarlh
– jarlh, Commented Oct 20, 2016 at 8:38
If datatype for "F_Asset_CurrentValue" is varchar this operation cannot be done. — yuvi
– yuvi, Commented Oct 20, 2016 at 8:40
@jarlh it is posible to do (F_Asset_CurrentValue - '99.58') operation only if "F_Asset_CurrentValue" dataype is not equal to varchar — yuvi
– yuvi, Commented Oct 20, 2016 at 8:42

David דודו Markovitz · Accepted Answer · 2016-10-20 08:52:04Z

3

You are getting this error because both sides of of the arithmetic operation are of character type.

In the following example there is an implicit conversion of '123' to 123

select      456   - '123';

In the following example there is an implicit conversion of '456' to 123

select      '456' - 123;

The following example yields in a error

select      '456' - '123';

Msg 402, Level 16, State 1, Line 1
The data types varchar and varchar are incompatible in the subtract operator.

My recommendation is to use explicit conversion

cast (F_Asset_CurrentValue as decimal (32,2)) - 99.58

answered Oct 20, 2016 at 8:52

45.2k7 gold badges75 silver badges97 bronze badges

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

1 Answer 1