Conversion failed when converting the nvarchar value 'test' to data type int
<p>Context: <strong>SQL Server 2008</strong></p>

<p>I have a table <code>mytable</code> which contains two <code>NVARCHAR</code> columns <code>id</code>, <code>title</code>.</p>

<p>All data in the <code>id</code> column are in fact numeric, except one row which contains the value 'test'.</p>

<p>I want to get all ids between 10 and 15 so I need SQL Server to convert id column values to <code>INTEGER</code>. </p>

<p>I use <code>ISNUMERIC(id) = 1</code> to eliminate the non numeric values first but SQL Server is being rather weird with this query.</p>

<pre><code>SELECT 
    in.* 
FROM 
    (SELECT 
         id, title 
     FROM 
         mytable 
     WHERE 
         ISNUMERIC(id) = 1) in
WHERE 
    in.id BETWEEN 10 AND 15
</code></pre>

<p>This query causes the following error:</p>

<blockquote>
  <p>Conversion failed when converting the nvarchar value 'test' to
  data type int.</p>
</blockquote>

<p>The inner query eliminates the row with the 'test' id value so 'in' shouldn't contain it. Why is SQL Server still trying to convert it?</p>

<p>Am I missing something? How can I get around this?</p>

<p>P.S. I tried <code>WHERE CAST(in.id AS INTEGER) BETWEEN 10 AND 15</code> but didn't work either.</p>