Filter by Dates in SQL

问题:

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.

回答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.

回答2:

WHERE dates BETWEEN (convert(datetime, '2012-12-12',110) AND (convert(datetime, '2012-12-12',110))

回答3:

Well you are trying to compare Date with Nvarchar which is wrong. Should be

Where dates between date1 And date2
-- both date1 & date2 should be date/datetime

If date1,date2 strings; server will convert them to date type before filtering.