performance: {ts 'datetime'} vs DATEADD() I received a claim for an incredible slow query in our print label software. With SQL Profiler I got the offender query and started to test in SSMS with really bad performance (around 00:50:00).
The query is long, and the tables are big, but there is a

WHERE 
 ...
 fieldX &gt; {ts '2016-01-01 00:00:00} /* fieldX is a DATETIME */
 ...


I changed to 

WHERE 
 ...
 fieldX &gt; DATEADD(YYYY, -1, GETDATE())
 ...


And the execution time now shows 00:00:00 !!

I just want understand why?
I recommended (originally, in the comments) that HEDMON try some different dates. Since his test date and his production dates were different, it seemed like it might be possible that there was a selectivity issue with the dates; that SQL could return the data much faster with a more recent date than with an older one.

HEDMON did try that, and found (from his comments) that dates from 2017-02-25 forward responded quickly, while dates older than that were slow.

With this, he felt comfortable that the presentation of the date (using the {ts } syntax) had nothing to do with the issue.