What about using DATEDIFF on days where it equals zero?

Still, joining on something like this is going to be problematic. Most problematic of all, you're joining on columns that clearly have no referential integrity. You can't reliably put these together.

Nice article.

You didn't mention implicit functions, like comparing a string to a datetime or an int to a smallint where SQL Server supplies a function for you even though you didn't write one.