The conversion of nvarchar value "0854697543" overflowed an int column in select command

Question

I have a select command, and it returns answer records, but it also gives an error in Visual Studio 2010's query builder with this query:

SELECT  *
FROM    Orders
WHERE   (BCode = 025) AND (Date BETWEEN '1390%' AND '1391%') OR
        (Date BETWEEN '1390%' AND '1391%') AND (MCode = 0123456789)

The error is:

Error Message: the conversion of nvarchar value "0854697543"
overflowed an int column

Data types are

BCode : nvarchar(50)
Date :  nvarchar(50)
MCode : nvarchar(10)

Where is the problem?

Answer 1

Shouldn't it be

AND (MCode = '0123456789')

?

Otherwise it will try to use 0123456789 as an integer which will lead to the conversion error.

In addition, you're repeating yourself in the logic (Date BETWEEN...), more concise:

WHERE   (Date BETWEEN '1390%' AND '1391%') AND
        ((BCode = 025) OR (MCode = '0123456789'))

Answer 2

I think I understand where the problem is: you're comparing MCode (which is a nvarchar) with an integer value (0123456789) in your query, so engine is trying to convert your field to int for every record!! Try this

SELECT  *
FROM    Orders
WHERE   (BCode = '025') AND 
        (LEFT(Date, 4) = '1390' OR LEFT(Date, 4) = '1391') AND 
        (MCode = '0123456789')

As I've already told you in my comment: don't use nvarchar(50) to store dates!! Use appropriate data type (DateTime or Date for example) for every column: it will avoid headaches in future, makes query design easier and makes query execution really faster!