Why is my MySQL `BETWEEN` clause date range query returning no results?

Solve MySQL date range query problem

This article explores a common problem encountered when using MySQL to query a specified date range. The following query statement:

<code class="language-sql">SELECT * FROM `objects` 
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')</code>

Although there is data in the database that meets the condition, the statement cannot return any results.

The problem is the order of the dates in the BETWEEN clause. The second date ('2010-01-30 14:15:55') is earlier than the first date ('2010-09-29 10:15:55'), resulting in a generated date range in which no data actually exists . To fix this, just reverse the date order:

<code class="language-sql">SELECT *
FROM `objects`
WHERE (date_field BETWEEN '2010-09-29 10:15:55' AND '2010-01-30 14:15:55')</code>

This way, the query will find objects with a date_field value between September 29, 2010 and January 30, 2010, thus getting the expected results.

For more information, please refer to the official MySQL documentation: https://www.php.cn/link/4c74dcfeac90df69aed5c8a90125e696

The above is the detailed content of Why is my MySQL `BETWEEN` clause date range query returning no results?. For more information, please follow other related articles on the PHP Chinese website!