In SQL Server, what is the best way to filter items for an entire day

Question

In SQL Server, I want to show all items for a particular day. What is the best way to do this: DATEPART, BETWEEN, OR >= + < ? All 3 should work functionally, but are some of them better/worse for performance?

1)

SELECT *
FROM BlogPosts
WHERE DATEPART(yyyy,BlogPostDate) = 2011
    AND DATEPART(m,BlogPostDate) = 5
    AND DATEPART(d,BlogPostDate) = 7

2)

SELECT *
FROM BlogPosts
WHERE BlogPostDate BETWEEN '2011-05-07' AND '2011-05-07 23:59:59'

3)

  SELECT *
    FROM BlogPosts
    WHERE BlogPostDate >= '2011-05-07' 
        AND BlogPostDate < '2011-05-08'

Answer 1

Out of the options you have presented. Number 3 as it is both sargable and will work.

Number 1 is not sargable (can't use an index).

Number 2 will miss valid datetimes such as 2011-05-07 23:59:59.997 (and the exact maximum datetime that you would have to use here would vary between smalldatetime, datetime, and datetime2(x))

If you are on SQL Server 2008 an alternative option which is sargable despite appearances is

 SELECT *     
 FROM BlogPosts     
 WHERE CAST(BlogPostDate AS Date)  = '2011-05-07'

However I would still opt for your Number 3 with the >= .... < as being more efficient both in terms of the range seeked and also potentially making better use of column statistics. More details of that here.