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

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?

Get items when the date is between fromdate and toDate.

where convert(date, fromdate, 103 ) <= '2016-07-26' and convert(date, toDate, 103) >= '2016-07-26'

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

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.