title: Conversion to datetime fails only on WHERE clause?
body: <p>I'm having a problem with some SQL server queries. Turns out that I have a table with "Attibute_Name" and "Attibute_Value" fields, which can be of any type, stored in varchar. (Yeah... I know.)</p>
  
  <p>All the dates for a particular attribute seem to be stored the the "YYYY-MM-DD hh:mm:ss" format (not 100% sure about that, there are millions of records here), so I can execute this code without problems:</p>
  
  <pre><code>select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
  from 
      ProductAttributes pa
      inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
  where 
      a.Attribute_Name = 'SomeDate'
  </code></pre>
  
  <p>However, if I execute the following code:</p>
  
  <pre><code>select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
  from 
      ProductAttributes pa
      inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
  where 
      a.Attribute_Name = 'SomeDate'
      and CONVERT(DATETIME, pa.Attribute_Value) &lt; GETDATE()
  </code></pre>
  
  <p>I will get the following error:
  <i>Conversion failed when converting date and/or time from character string.</i></p>
  
  <p><i><b>How come it fails on the where clause and not on the select one?</b></i></p>
  
  <p>Another clue:</p>
  
  <p>If instead of filtering by the Attribute_Name I use the actual Attribute_ID stored in database (PK) it will work without problem.</p>
  
  <pre><code>select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
  from 
      ProductAttributes pa
      inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
  where 
      a.Attribute_ID = 15
      and CONVERT(DATETIME, pa.Attribute_Value) &lt; GETDATE()
  </code></pre>
  
  <p><b>Update</b>
  Thanks everyone for the answers. I found it hard to actually choose a correct answer because everyone pointed out something that was useful to understanding the issue. It was definitely having to do with the order of execution.
  Turns out that my first query worked correctly because the WHERE clause was executed first, then the SELECT.
  My second query failed because of the same reason (as the Attributes were not filtered, the conversion failed while executing the same WHERE clause).
  My third query worked because the ID was part of an index (PK), so it took precedence and it drilled down results on that condition first.</p>
  
  <p>Thanks!</p>