Search This Blog

Operand data type nvarchar is invalid for subtract operator.

Operand data type nvarchar is invalid for subtract operator.

Issue- In one of the SQL Groups forum one of the user asked a question that we want the diff of Max value of one column and min value of other column

 Difference=Max(Column1)-Min(Column2)

Below is the user Original question

Question-i am facing a small problem .kindly help me
select min(low) as low, max(high) as high from <table_name>
now i want to find the difference between two results 
i used 
select (select max(high)from <table_name>)-(select min(low)from <table_name>) as diff
This is not working ... kindly help me
Table screenshot


Solution-So for this I created a test script with same table structure so below is the script with output

create table x1
(name nvarchar(100),
high float,
low float
)

insert into x1 values ('xyz', 50.5, 48),('xyz', 60.2 ,50.3),('xyz', 55, 45),('xyz' ,62.9 ,47.6)



select max(high)MaxHigh,min(low)MinLow ,max(high)-min(low) differences from x


Output

MaxHigh           MinLow differences
62.9                  45                     17.9

After giving the Query to him he informed me that he is getting below error

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

 In case if you are applying multiplication then you will get the below error.

Msg 8117, Level 16, State 1, Line 1

Operand data type nvarchar is invalid for multiply operator

In case if you are applying divide then you will get the below error.

Msg 8117, Level 16, State 1, Line 1

Operand data type nvarchar is invalid for divide operator.


These Errors means columns are not int or float type they are varchar type so i casted them into float to give him result without any error.

Solution with casting the varchar field to float.

select max( cast(high as float))-min(cast(low as float)) diff from x

Output

diff
17.9

or as per his query

select (select max( cast(high as float))from x)-(select min(cast(low as float)) from x) as diff

Output

diff
17.9


NOTE: Always keep in mind that applying any arithmetic operator or function in any varchar field you need to first cast or convert it into int, float or decimal. 


No comments:

Post a Comment