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

Question

I have a SQL datetime field in a very large table. It's indexed and needs to be queried.

The problem is that SQL always stores the time component (even though it's always midnight), but the searches are to the day, rather than time.

declare @dateVar datetime = '2013-03-11;

select t.[DateColumn]
from MyTable t
where t.[DateColumn] = dateVar;

Won't return anything, as the t.[DateColumn] always includes a time component.

My question is what is the best way round this?

There seem to be two main groups of options:

1. Create a second variable using dateadd and use a between ... and or >= ... and ... <=.

2. Convert the t.[DateColumn] into a date-only component - I think this will cause any indexes to be ignored.

Both of these seem very messy - I don't really want to be making a range comparison or scan the table.

Is there a better way?

If one of these options is consistently optimal way then how and why?

Solution 1

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 t
WHERE = 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 t
WHERE t.[DateColumn] >= @dateVar AND 
      t.[DateColumn] < @dateEnd;

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

Solution 2

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') = 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'

Is found here