SQL DateTime and comparison

Today I had to modify some SQL code for an application and this work was the start for a discussion: what's the best way to check for a SQL DateTime field with only a given date?

For example, I've an Order table with a DateTime field called OrderDate. I want to retrieve all orders where the order date is equals to 01/01/2006. What's the best way?

  1. WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
  2. WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
  3. WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
  4. WHERE OrderDate LIKE '01/01/2006%'
  5. WHERE OrderDate >= '01/01/2006'  AND OrderDate < '01/02/2006'
  6. ...

In my opinion the best way in terms of performances is the 5) and it's one of my personal "best practices" when this type of comparison is required. It requires 2 tests but it's better than calling special T-SQL functions like the others.

Any other ideas?

P.S. What is absolutely to avoid is the using of the LIKE function... terrible!

Print | posted on Thursday, January 19, 2006 11:06 AM