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?
- WHERE DateDiff(dd, OrderDate, '01/01/2006') =
- WHERE Convert(varchar(20), OrderDate, 101) =
- WHERE Year(OrderDate) = 2006 AND Month(OrderDate)
= 1 and Day(OrderDate)=1
- WHERE OrderDate LIKE '01/01/2006%'
- WHERE OrderDate >= '01/01/2006' AND
OrderDate < '01/02/2006'
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
Any other ideas?
P.S. What is absolutely to avoid is the using of the LIKE