CODEWITHSUNDEEP
sqlsql-server
Giorgi Moniava
Giorgi Moniava

Reputation: 28685

Conversion failed when converting the nvarchar value 'S' to data type int

I was playing with SQL. AdventureWorks2012 database, table: Production.Products.

I was trying to get products which have color black and: either have weight NULL or their size and listPrice is above some value.

Here is query

SELECT * 
FROM AdventureWorks2012.Production.Product 
WHERE (Color = 'Black') 
  AND ((ListPrice > 300 AND Size > 60) OR Weight IS NULL)

But this is the error I get

Conversion failed when converting the nvarchar value 'S' to data type int.

Can anyone clarify what I did wrong?

Upvotes: 1

Views: 7301

Answers (6)

John Cappelletti
John Cappelletti

Reputation: 82010

Perhaps use Try_Convert(). This will return NULL for invalid conversions

SELECT * FROM Production.Product 
 WHERE (Color = 'Black') 
   AND ((ListPrice > 300 AND try_convert(int,Size) > 60) OR Weight IS NULL)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69574

I think the Size column has data like S,L,M,XL hence the data conversion fails. Since you are looking for values > 60 , int being higher presidence data type sql server tries to convert S,L,M,XL values to Int and fails hence the error.

Easier fix: instead of using 60 use string containing values '60' for column size.

Something like...

SELECT * 
FROM AdventureWorks2012.Production.Product 
WHERE (Color = 'Black') 
AND ( (ListPrice > 300 AND Size > '60') 
      OR [Weight] IS NULL)

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32220

Size > 60

This is a numeric comparison. The query engine will try to convert every value of Size to the same type as 60, which is an integer. Try:

Size > '60'

But, of course, that's an alphanumeric comparison, so it will be strings that are alphabetically after the string '60'. You may need to employ TRY_CAST() or TRY_CONVERT().

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

Size is nvarchar(5), so that's where your S is coming from.

Check out the schema for Adventureworks

Upvotes: 2

Ole EH Dufour
Ole EH Dufour

Reputation: 3240

Size = 'S' means 'Small' ? Do you know what datatype is used for the column 'Size' ? If it's a single character like S for Small and L for Large, you cannot compare it to an integer.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

The error says it all, your column(ListPrice or Size) is containing a value as 'S' and you are trying to perform numeric comparison operation(>) on the column which results in the error.

Upvotes: 1

Related Questions