SQL Server fetching records between two dates

Question

In SQL I write a SELECT statement to fetch data between two dates  using between and   Ex   select    from xxx  where dates between  2012-10-26  and  2012-10-27    But the rows returned are for 26th only  not 26th and 27th   Can you help me  Thank you

User · Answer

The unambiguous way to write this is  i e  increase the 2nd date by 1 and make it  lt    select    from xxx  where dates  gt    20121026    and dates  lt    20121028    If you re using SQL Server 2008 or above  you can safety CAST as DATE while retaining SARGability  e g   select    from xxx  where CAST dates as DATE  between  20121026  and  20121027    This explicitly tells SQL Server that you are only interested in the DATE portion of the dates column for comparison against the BETWEEN range

User · Answer

try to use following query  select    from xxx  where convert date dates   gt    2012-10-26  and convert date dates   lt    2012-10-27

User · Answer

Try this   select    from xxx  where dates  gt    2012-10-26 00 00 00 000  and dates  lt    2012-10-27 23 59 59 997

User · Answer

Your question didnt ask how to use BETWEEN correctly  rather asked for help with the unexpectedly truncated results     As mentioned hinting at in the other answers  the problem is that you have time segments in addition to the dates    In my experience  using date diff is worth the extra wear tear on the keyboard  It allows you to express exactly what you want  and you are covered    select    from xxx  where datediff d   2012-10-26   dates   gt  0 and  datediff d  dates  2012-10-27    gt  0   using datediff  if the first date is before the second date  you get a positive number  There are several ways to write the above  for instance always having the field first  then the constant  Just flipping the operator   Its a matter of personal preference    you can be explicit about whether you want to be inclusive or exclusive of the endpoints by dropping one or both equal signs    BETWEEN will work in your case  because the endpoints are both assumed to be midnight  ie DATEs    If your endpoints were also DATETIME  using BETWEEN may require even more casting   In my mind DATEDIFF was put in our lives to insulate us from those issues

User · Answer

As others have answered  you probably have a DATETIME  or other variation  column and not a DATE datatype  Here s a condition that works for all  including DATE  SELECT    FROM xxx  WHERE dates  gt    20121026     AND dates  lt    20121028     --- one day after                               --- it is converted to  2012-10-28 00 00 00 000       Aaron Bertrand has blogged about this at  What do BETWEEN and the devil have in common

User · Answer

You need to be more explicit and add the start and end times as well  down to the milliseconds   select    from xxx  where dates between  2012-10-26 00 00 00 000  and  2012-10-27 23 59 59 997    The database can very well interpret  2012-10-27  as  2012-10-27 00 00 00 000

[sql-server] SQL Server : fetching records between two dates?

Examples related to sql-server

Examples related to datetime

Examples related to date-range