title: SQL Server : function precedence and short curcuiting in where clause
body: <p>Consider this setup:</p>
  
  <pre><code>create table #test (val varchar(10))
  insert into #test values ('20100101'), ('1')
  </code></pre>
  
  <p>Now if I run this query</p>
  
  <pre><code>select * 
  from #test
  where ISDATE(val) = 1
    and CAST(val as datetimeoffset) &gt; '2005-03-01 00:00:00 +00:00'
  </code></pre>
  
  <p>it will fail with </p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string</p>
  </blockquote>
  
  <p>which tells me that the <code>where</code> conditions are not short-circuited and both functions are evaluated. OK.</p>
  
  <p>However if I run </p>
  
  <pre><code>select * 
  from #test
  where LEN(val) &gt; 2
    and CAST(val as datetimeoffset) &gt; '2005-03-01 00:00:00 +00:00'
  </code></pre>
  
  <p>it doesn't fail, which tells me that <code>where</code> clause is short-circuited in this case.</p>
  
  <p>This</p>
  
  <pre><code>select * 
  from #test
  where ISDATE(val) = 1
    and CAST(val as datetimeoffset) &gt; '2005-03-01 00:00:00 +00:00'
    and LEN(val) &gt; 2
  </code></pre>
  
  <p>fails again, but if I move length check to before <strong>cast</strong>, it work. So looks like the functions are evaluated in the order they appear in query.</p>
  
  <p>Can anyone explain why first query fails?</p>