Just a suggestion...

The optimizer is going to evaluate the BETWEEN code in the quote above as ...

WHERE SomeDate >= '2008/01/01 12:00:00:000 AM'

AND SomeDate <= '2008/01/01 11:59:59:997 PM'

... anyway... that will be almost the same as what the code below which the optimizer also resolves as...

WHERE somedatecolumn >= '2008/01/01'

AND somedatecolumn < '2008/01/02'

The point is, there's no real performance or index usage difference so far as what the optimizer will do. But, there is the PITA factor if you want to return a whole month. For example, to return the whole month of January 2008 using the BETWEEN code, you must first know what the end date for the month is, add '23:59:59:997' to it and then use the BETWEEN.

To do the same thing using the AND method... you just add a month... and don't worry about what the end date for the month is...

WHERE somedatecolumn >= '2008/01/01'

AND somedatecolumn < '2008/02/01'

If you get into the habit of the AND method, you'll never forget to add the time, you'll never need to calulate the end date for a month, and the format holds true no matter which whole date/time element you're searching by. For example, you don't have to use '59:59:997' if you're seaching for a whole hour... just search for something LESS than the NEXT hour.

From a readability standpoint (at least to me), it's a lot easier on the eyes to look at whole dates that juggling '23:59:59:997' or just '59:59:997' everywhere in the code.

Anyway, like I said... just a suggestion...