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

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,BlogPostDa开发者_如何学Gote) = 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'

---

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.

---

Personally I prefer the DateDiff technique below.

DECLARE @selectedDate datetime = '2011-05-07'

SELECT * 
  FROM BlogPosts
 WHERE DateDiff(DAY,BlogPostDate, @selectedDate) = 0

---

Options 1) and 2) will both work.

Option 3 will not work as you expect, since datetime has a higher precision than seconds. So the where clause:

WHERE BlogPostDate BETWEEN '2011-05-07' AND '2011-05-07 23:59:59'

Would incorrectly filter out:

'2011-05-07 23:59:59.500'

Datetime values are rounded to increments of .000, .003, or .007 seconds. So this WHERE clause would work:

WHERE BlogPostDate BETWEEN '2011-05-07' AND '2011-05-07 23:59:59.997'