sql – How do I query between two dates using MySQL?

The Question :

240 people think this question is useful

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?

The Question Comments :
  • Be cautious with BETWEEN, as both min and max values are considered to be in the range, to not process twice a date that is either the min and max value (edge case). For instance, the date 2010-09-29 00:00:00 will be between 2010-09-28 00:00:00 and 2010-09-29 00:00:00, AND ALSO between 2010-09-29 00:00:00 and 2010-09-30 00:00:00
  • yea, what he said ^^

The Answer 1

498 people think this answer is useful

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')

The Answer 2

27 people think this answer is useful

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')

The Answer 3

20 people think this answer is useful

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')

The Answer 4

16 people think this answer is useful

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';

The Answer 5

8 people think this answer is useful

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')

The Answer 6

4 people think this answer is useful

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  >=   lower_date  and  created_at_column <= 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  >=   '2018-09-01 00:00:00'  and  created_at_column <= '2018-09-05 23:59:59';

The Answer 7

2 people think this answer is useful

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))

The Answer 8

1 people think this answer is useful

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.

The Answer 9

1 people think this answer is useful

Just Cast date_field as date

SELECT * FROM `objects` 
WHERE (cast(date_field as date) BETWEEN '2010-09-29' AND 
'2010-01-30' )

The Answer 10

0 people think this answer is useful

Try switching the dates around:

2010-09-29 > 2010-01-30?

Tags:,

Add a Comment