The mechanism behind the sargability of casting to date is called dynamic seek.
One disadvantage of relying on it is that the cardinality estimates may not be as accurate as with the traditional range query. This can be seen in an amended version of your SQL Fiddle.
All 256 rows in the table now match the predicate (with datetimes 1 minute apart all on the same day).
The second (range) query correctly estimates that 256 will match and uses a clustered index scan. The
CAST( AS DATE) query incorrectly estimates that only one row will match and produces a plan with key lookups.
I'm not sure where the 1 row estimate comes from. The statistics aren't ignored completely. If all rows in the table have the same
datetime and it matches the predicate (e.g.
20130101 00:00:00 or
20130101 01:00:00) then the plan shows a clustered index scan with an estimated 64 rows (25%).
If all rows in the table have the same
datetime and it doesn't match the predicate (e.g.
20130102 01:00:00) then it falls back to the estimated row count of 1 and the plan with lookups.