Derik Hammer shows the right way and the wrong way of using date functions in a WHERE clause:
I then changed my thought process to find the age of a 65 year old who’s birth day is today. Then I compare the DateOfBirth column to that static value.
SELECT COUNT(*) FROM dbo.Person p WHERE DateOfBirth <= DATEADD(YEAR,-65,GETDATE())With the above query I bought myself an index seek and 345 logical reads. That works out to <3% of the cost.
This is true not just for date functions, but rather is applicable to almost all scalar functions.