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 ="
Thanks for various options and description guys. Got it working with @RichardTheKiwi's options.
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.
WHERE dates BETWEEN (convert(datetime, '2012-12-12',110) AND (convert(datetime, '2012-12-12',110))
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.