SQL between not inclusive

Question

I have a query like this   SELECT   FROM Cases WHERE created at BETWEEN  2013-05-01  AND  2013-05-01    But this gives no results even though there is data on the 1st   created at looks like 2013-05-01 22 25 19  I suspect it has to do with the time  How could this be resolved   It works just fine if I do larger date ranges  but it should  inclusive  work with a single date too

User · Answer

You need to do one of these two options    Include the time component in your between condition      where created at between  2013-05-01 00 00 00  and  2013-05-01 23 59 59   not recommended    see the last paragraph  Use inequalities instead of between  Notice that then you ll have to add one day to the second value      where  created at  gt    2013-05-01  and created at  lt   2013-05-02     My personal preference is the second option  Also  Aaron Bertrand has a very clear explanation on why it should be used

User · Answer

your code SELECT   FROM Cases WHERE created at BETWEEN  2013-05-01  AND  2013-05-01   how SQL reading it SELECT   FROM Cases WHERE  2013-05-01 22 25 19  BETWEEN  2013-05-01 00 00 00  AND  2013-05-01 00 00 00   if you don t mention time while comparing DateTime and Date by default hours minutes seconds will be zero in your case dates are the same but if you compare time created at is 22 hours ahead from your end date range if the above is clear you fix this in many ways like putting  ending hours in your end date eg BETWEEN  2013-05-01  AND   2013-05-01 23 59 59   OR simply cast create at as date like cast created at as date  after casting as date  2013-05-01 22 25 19  will be equal to  2013-05-01 00 00 00

User · Answer

Just use the time stamp as date   SELECT   FROM Cases WHERE date created at   2013-05-01

User · Answer

cast created at as date    That will work only in 2008 and newer versions of SQL Server  If you are using older version then use    convert varchar  created at  101

User · Answer

Dyamic date BETWEEN sql query  var startDate    2019-08-22   var Enddate    2019-10-22       let sql    SELECT   FROM Cases WHERE created at BETWEEN     AND            const users   await mysql query  sql   startDate  Enddate

User · Answer

I find that the best solution to comparing a datetime field to a date field is the following   DECLARE  StartDate DATE    5 1 2013             EndDate   DATE    5 1 2013    SELECT    FROM   cases  WHERE  Datediff day  created at   StartDate   lt   0         AND Datediff day  created at   EndDate   gt   0    This is equivalent to an inclusive between statement as it includes both the start and end date as well as those that fall between

User · Answer

You can use the date   function which will extract the date from a datetime and give you the result as inclusive date   SELECT   FROM Cases WHERE date created at   2013-05-01  AND  2013-05-01

User · Answer

It is inclusive   You are comparing datetimes to dates   The second date is interpreted as midnight when the day starts   One way to fix this is   SELECT   FROM Cases WHERE cast created at as date  BETWEEN  2013-05-01  AND  2013-05-01    Another way to fix it is with explicit binary comparisons  SELECT   FROM Cases WHERE created at  gt    2013-05-01  AND created at  lt   2013-05-02    Aaron Bertrand has a long blog entry on dates  here   where he discusses this and other date issues

User · Answer

It has been assumed that the second date reference in the BETWEEN syntax is magically considered to be the  end of the day  but this is untrue   i e  this was expected    SELECT   FROM Cases  WHERE created at BETWEEN the beginning of  2013-05-01  AND the end of  2013-05-01   but what really happen is this    SELECT   FROM Cases  WHERE created at BETWEEN  2013-05-01 00 00 00 00000  AND  2013-05-01 00 00 00 00000    Which becomes the equivalent of    SELECT   FROM Cases WHERE created at    2013-05-01 00 00 00 00000      The problem is one of perceptions expectations about BETWEEN which does include BOTH the lower value and the upper values in the range  but does not magically make a date the  beginning of  or  the end of    BETWEEN should be avoided when filtering by date ranges   Always use the  gt   AND  lt  instead    SELECT   FROM Cases  WHERE  created at     20130501  AND created at  lt   20130502    the parentheses are optional here but can be important in more complex queries

[sql] SQL "between" not inclusive

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to date