Normally when running a query against a table and using a CAST or CONVERT function against a datetime field any index is made useless. In SQL Server 2008 this problem is fixed. Microsoft has come up with a way for SELECT statements which use CAST or CONVERT against a column of the datetime datatype to continue to use the index. Now keep in mind that this only works for the datetime datatypes and not other data types.
I believe that this feature showed up in CTP 5 (November).
DennyContact the Author | Contact DCAC