Filter by Dates in SQL

Question

I have a column in my table for dates (DateTime) and I am trying to create a WHERE clause that says, WHERE dates BETWEEN 12-11-2012 and 12-13-2012

A sample value of dates column = 2012-05-24 00:38:40.260

I want to say WHERE dates BETWEEN MM-DD-YYYY and MM-DD-YYYY.

I tried doing

WHERE dates BETWEEN ((convert(nvarchar(10), dates,110) = '2012-12-12') AND (convert(nvarchar(10), dates,110) = '2012-12-12')) 

but doesn't seem to work. "Incorrect syntax near ="

Please help

EDIT:

Thanks for various options and description guys. Got it working with @RichardTheKiwi's options.

Answer 1

If your dates column does not contain time information, you could get away with:

WHERE dates BETWEEN '20121211' and '20121213' 

However, given your dates column is actually datetime, you want this

WHERE dates >= '20121211'   AND dates < '20121214'  -- i.e. 00:00 of the next day 

Another option for SQL Server 2008 onwards that retains SARGability (ability to use index for good performance) is:

WHERE CAST(dates as date) BETWEEN '20121211' and '20121213' 

Note: always use ISO-8601 format YYYYMMDD with SQL Server for unambiguous date literals.