What is the optimal way to compare dates in Microsoft SQL server?

Converting to a DATE or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime? Posted by Aaron Bertrand

From that article:

DECLARE @dateVar datetime = '19700204';-- Quickest when there is an index on t.[DateColumn], -- because CONVERT can still use the index.SELECT t.[DateColumn]FROM MyTable tWHERE = CONVERT(DATE, t.[DateColumn]) = CONVERT(DATE, @dateVar);-- Quicker when there is no index on t.[DateColumn]DECLARE @dateEnd datetime = DATEADD(DAY, 1, @dateVar);SELECT t.[DateColumn] FROM MyTable tWHERE t.[DateColumn] >= @dateVar AND       t.[DateColumn] < @dateEnd;

Also from that article: using BETWEEN, DATEDIFF or CONVERT(CHAR(8)... are all slower.

Here is an 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. there are next ways to do it:

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

Is found here

You could add a calculated column that includes only the date without the time. Between the two options, I'd go with the BETWEEN operator because it's 'cleaner' to me and should make better use of indexes. Comparing execution plans would seem to indicate that BETWEEN would be faster; however, in actual testing they performed the same.