How to Select Data Between a Date/Time Range in MySQL?

Selecting Data Between a Date/Time Range in MySQL

In MySQL, using the BETWEEN operator can facilitate selecting data that falls within a specific date or time range. When working with datetime columns, it is crucial to ensure that the values used in your query are formatted correctly.

The example provided in the question attempts to select data between two dates in 24-hour Zulu time format. However, an unexpected result is encountered due to an incorrect date 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;

The dates in the query utilize a non-standard format, which causes the comparison to fail. To resolve this issue, the date values must be reformatted into a form recognizable by MySQL.

Correct Solution:

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;

Here, the dates are adjusted to conform to the ISO 8601 standard, ensuring compatibility with MySQL's datetime datatype. This modification allows for the accurate selection of data within the specified date range.

The above is the detailed content of How to Select Data Between a Date/Time Range in MySQL?. For more information, please follow other related articles on the PHP Chinese website!