Every database developer has had to remove the time from a datetime value once. Comparing dates with time included regularly renders false results. The most common approach is something like this:
This approach does not actually remove the time from the datetime. It just moves it to midnight. To be fair, this is the best option SQL developers have had before the DATE data type in SQL 2008.
Recently I discovered a second approach to removing time from a datetime value. The technique finds the distance from midnight, the moves the time back to midnight. The syntax looks like this:
What's bad about this technique is that it is hard to remember and type from the top of your head (and get right the first time). What's good about this approach, and surprising to me, is that it is the fastest technique - probably because it does not have to handle a conversion.
I tested this to make sure. Here's the test script:
Get the code here: http://www.codepaste.net/rr8nru
This takes about a minute to execute. It attempts many different types of conversion, including the ones that only SQL 2008 can support (remember that if you run it yourself).
Where I would have expected the classic VARCHAR(101) to perform well, it didn't. Not at all. The DATEADD technique nearly beats the Date data type in SQL 2008. Here are the crazy results:
Conclusion: all the techniques are within milliseconds of each other. It makes sense to code in a away that future developers can understand. If varchar(101) is easiest in your shop, then keep on keeping on. But if you are targeting sheer performance, consider the DATEADD technique.