title: Execution plan changes when using hard-coded date vs. using variable date
body: <p>This query:</p>
  
  <pre><code>select * from mytable where mytable.somedate = '08 Sep 2015'
  </code></pre>
  
  <p>Produces a different execution plan to this query:</p>
  
  <pre><code>-- (people from the future - assume today is 08 Sep 2015)
  select * from mytable where mytable.somedate = dateadd(day, datediff(day, 0, getdate()), 0)
  </code></pre>
  
  <p>Which isn't a problem in this tiny example query, but as part of a much larger query there's a very, very large performance hit. The version of the query with a <code>where mytable.somedate = '08 Sep 2015'</code> takes under a second, the version with a <code>where mytable.somedate = dateadd(...)</code> takes <em>over 30 seconds</em>, due to only this change.</p>
  
  <p>The type of <code>somedate</code> is <code>datetime</code>, and is indexed. I tried putting <code>dateadd(day, datediff(day, 0, getdate()), 0)</code> into a variable, and I've tried various datetime casts on both sides of the clause. I've also tried different ways of getting today's date without a timestamp (eg <code>cast(getdate() as date)</code>). All produce the slow execution plan - it appears only a hardcoded date produces the fast plan. A hardcoded date can't be used, as the query needs to return records for <em>today</em>, whatever day <em>today</em> is.</p>
  
  <p>How can I make this run quickly?</p>