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

User · Answer

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.

User · Answer

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  gt    20121211    AND dates  lt   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

User · Answer

WHERE dates BETWEEN  convert datetime   2012-12-12  110  AND  convert datetime   2012-12-12  110

[sql] Filter by Dates in SQL

Examples related to sql

Examples related to sql-server

Examples related to tsql