How do I query between two dates using MySQL

Question

The following query   SELECT   FROM  objects   WHERE  date field BETWEEN  2010-09-29 10 15 55  AND  2010-01-30 14 15 55     returns nothing   I should have more than enough data to for the query to work though  What am I doing wrong

User · Answer

When using Date and Time values  you must cast the fields as DateTime and not Date  Try    SELECT   FROM  objects   WHERE  CAST date field AS DATETIME   BETWEEN CAST  2010-09-29 10 15 55  AS DATETIME  AND CAST  2010-01-30 14 15 55  AS DATETIME

User · Answer

As extension to the answer from  sabin and a hint if one wants to compare the date part only  without the time    If the field to compare is from type datetime and only dates are specified for comparison  then these dates are internally converted to datetime values  This means that the following query  SELECT   FROM  objects  WHERE  date time field BETWEEN  2010-01-30  AND  2010-09-29     will be converted to   SELECT   FROM  objects  WHERE  date time field BETWEEN  2010-01-30 00 00 00  AND  2010-09-29 00 00 00     internally   This in turn leads to a result that does not include the objects from 2010-09-29 with a time value greater than 00 00 00   Thus  if all objects with date 2010-09-29 should be included too  the field to compare has to be converted to a date   SELECT   FROM  objects  WHERE  DATE date time field  BETWEEN  2010-01-30  AND  2010-09-29

User · Answer

DATE   is a MySQL function that extracts only the date part of a date or date time expression  SELECT   FROM table name WHERE DATE date field  BETWEEN  2016-12-01  AND  2016-12-10

User · Answer

Your query should have date as   select   from table between  lowerdate  and  upperdate    try  SELECT   FROM  objects   WHERE   date field BETWEEN  2010-01-30 14 15 55  AND  2010-09-29 10 15 55

User · Answer

You can do it manually  by comparing with greater than or equal and less than or equal    select   from table name where created at column   gt     lower date  and  created at column  lt   upper date    In our example  we need to retrieve data from a particular day to day   We will compare from the beginning of the day to the latest second in another day     select   from table name where created at column   gt      2018-09-01 00 00 00   and  created at column  lt    2018-09-05 23 59 59

User · Answer

Try switching the dates around   2010-09-29  gt  2010-01-30

User · Answer

Is date field of type datetime   Also you need to put the eariler date first   It should be   SELECT   FROM  objects   WHERE   date field BETWEEN  2010-01-30 14 15 55  AND  2010-09-29 10 15 55

User · Answer

Just Cast date field as date    SELECT   FROM  objects   WHERE  cast date field as date  BETWEEN  2010-09-29  AND   2010-01-30

User · Answer

Might be a problem with date configuration on server side or on client side  I ve found this to be a common problem on multiple databases when the host is configured in spanish  french or whatever    that could affect the format dd mm yyyy or mm dd yyyy

User · Answer

Your second date is before your first date  ie  you are querying between September 29 2010 and January 30 2010   Try reversing the order of the dates   SELECT   FROM  objects  WHERE  date field BETWEEN  2010-01-30 14 15 55  AND  2010-09-29 10 15 55

[mysql] How do I query between two dates using MySQL?

Examples related to mysql

Examples related to sql