Select data between a date time range

Question

How do I select data between a date range in MySQL  My datetime column is in 24-hour zulu time format    select   from hockey stats  where game date between  11 3 2012 00 00 00  and  11 5 2012 23 59 00   order by game date desc    Returns nothing despite having data between these time periods  Do I have to force the values in the  from  and  to  fields to datetime type in the query

User · Accepted Answer

You need to update the date format   select   from hockey stats  where game date between  2012-03-11 00 00 00  and  2012-05-11 23 59 00   order by game date desc

User · Answer

You can either user STR TO DATE function and pass your own date parameters based on the format you have posted    select   from hockey stats where game date    between STR TO DATE  11 3 2012 00 00 00     c  e  Y  H  i  s     and STR TO DATE  11 5 2012 23 59 00     c  e  Y  H  i  s    order by game date desc    Or just use the format which MySQL handles dates YYYY MM DD HH mm SS and have the query as   select   from hockey stats where game date between  2012-03-11 00 00 00  and 2012-05-11 23 59 00  order by game date desc

User · Answer

You must search date defend on how you insert that game date data on your database   for example if you inserted date value on long date or short   SELECT   FROM hockey stats WHERE game date  gt    6 11 2018  AND game date  lt    6 17 2018    You can also use BETWEEN   SELECT   FROM hockey stats WHERE game date BETWEEN  6 11 2018  AND  6 17 2018    simple as that

User · Answer

Here is a simple way using the date function   select   from hockey stats where date game date  between date  2012-11-03   and date  2012-11-05   order by game date desc

User · Answer

A simple way     select    from  hockey stats  where  game date  gt    2012-03-11  and game date   lt    2012-05-11

User · Answer

You probably need to use STR TO DATE function   select   from hockey stats  where   game date between STR TO DATE  11 3 2012 00 00 00     c  e  Y  H  i  s                   and STR TO DATE  11 5 2012 23 59 00     c  e  Y  H  i  s    order by game date desc     if game date is a string  you might need to use STR TO DATE on it

User · Answer

MySQL date format is this   Y-M-D  You are using Y M D  That s is wrong   modify your query    If you insert the date like Y M D  It will be insert null value in the database    If you are using PHP and date you are getting from the form is like this Y M D  you can replace this with using the statement     out date date  Y-m-d   strtotime str replace       -    data  input date

User · Answer

In a simple way it can be queried as  select   from hockey stats  where game date between  2018-01-01  and  2018-01-31     This works if time is not a concern   Considering time also follow in the following way   select   from hockey stats where  game date between  2018-02-05 01 20 00  and  2018-02-05 03 50 00      Note this is for MySQL server

[mysql] Select data between a date/time range

Examples related to mysql

Examples related to datetime