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

sql selectcommand
hamze picture hamze · Nov 17, 2011 · Viewed 13.2k times · Source

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

beny23 picture beny23 · Nov 17, 2011

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'))

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More