title: SQL Server : error converting data type varchar to numeric
body: <p>I have a table:</p>
  
  <pre><code>Account_Code | Desc
  503100       | account xxx
  503103       | account xxx
  503104       | account xxx
  503102A      | account xxx
  503110B      | account xxx
  </code></pre>
  
  <p>Where <code>Account_Code</code> is a <code>varchar</code>.</p>
  
  <p>When I create a query below:</p>
  
  <pre><code>Select 
    cast(account_code as numeric(20,0)) as account_code,
    descr 
  from account 
  where isnumeric(account_code) = 1
  </code></pre>
  
  <p>It runs well by returning all record that have a valid numeric value in <code>account_code</code> column.</p>
  
  <p>But when I try to add another select, nested to prior sql:</p>
  
  <pre><code>select account_code,descr 
  from 
  (
    Select cast(account_code as numeric(20, 0)) as account_code,descr 
    from account 
    where isnumeric(account_code) = 1
  ) a 
  WHERE account_code between 503100 and 503105
  </code></pre>
  
  <p>the query will return an error </p>
  
  <blockquote>
    <p>Error converting data type varchar to numeric.</p>
  </blockquote>
  
  <p>What is happening there? </p>
  
  <p>I have already converted to numeric if <code>account_code</code> valid, but it seems the query is still trying to process a non valid record.</p>
  
  <p>I need to use  <code>BETWEEN</code> clause in my query.</p>