query question

Hi Mike - So i tried it this way and here is the error i get.

declare @accountnumber varchar(10);
set @accountnumber = '0250050004';
select top 10 * from billing.account where accountnumber between @accountnumber-2 and @accountnumber+2


Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the nvarchar value '0530433004BAD' to data type int.

I tried the following using my table and it works

declare @accountnumber int--varchar(10);
set @accountnumber = 7778;
select top 10 * from tbl2 where AcctNo between @accountnumber-2 and @accountnumber+2

Select allOpen in new window

Note that in tbl2, the AcctNos are all text.

AcctNo  AcctName
7776	      B
7777	      C
7778	      D
7779	      E
7780	      F

Select allOpen in new window

declare @accountnumber int--varchar(10);
set @accountnumber = 02500500004;
select top 10 * from billing.account where Accountnumber between @accountnumber-2 and @accountnumber+2

ok so i tried this and here is the error i get...  do you think it might have to do with the fact
that i have leading zeros

Msg 8115, Level 16, State 2, Line 17
Arithmetic overflow error converting expression to data type int.
Msg 245, Level 16, State 1, Line 18
Conversion failed when converting the nvarchar value '0530433004BAD' to data type int.

In '0530433004BAD'

is BAD a part of the account number? You did say they are text but you didn't say they are alphanumeric.

the accounts are all text and most of them have either 1 or 2 leading zeros.  there are no letters in the accountnumber eventhough they are varchar

Could you please run this to see what you get? It should return zero rows because you mentioned there is no account number like '0530433004BAD'

select * from billing.account where Accountnumber like '%BAD'

Select allOpen in new window

If it returns one or more records then we have to rethink the solution.

well this is weird
there is one accountnumber that has bad in it 0530433004BAD

I have to leave for tonight....
thanks for all your help
i think i am closer

Are you going to remove it or we have to go around it?

thanks guys