3

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
hamze
  • 7,061
  • 6
  • 34
  • 43
  • 1
    Can you tell us in which field is present `0854697543` value? And why do you use `nvarchar` for dates? – Marco Nov 17 '11 at 14:35
  • And why are you storing a date value as `nvarchar(50)` ?? – marc_s Nov 17 '11 at 14:38
  • 1
    What is the `BETWEEN '1390%' AND '1391%'` meant to do? As the '%' wildcard operator does not apply in the `BETWEEN` clause. – beny23 Nov 17 '11 at 14:51

2 Answers2

2

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'))
beny23
  • 34,390
  • 5
  • 82
  • 85
1

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!

Marco
  • 56,740
  • 14
  • 129
  • 152