How to Select MySQL Records from the Last 30 Days, Including Today?

Selecting Records Between Today and the Last 30 Days in MySQL

In MySQL, you can retrieve records that fall within a specified date range using a query like the following:

SELECT *
FROM table_name
WHERE date_column BETWEEN start_date AND end_date

However, when attempting to limit the results to the previous 30 days, users may encounter issues where the query returns all records instead. To address this, it is crucial to understand proper date formatting syntax.

In the provided query:

create_date between DATE_FORMAT(curdate(),'%m/%d/%Y') AND (DATE_FORMAT(curdate() - interval 30 day,'%m/%d/%Y'))

The DATE_FORMAT function is incorrectly used within the WHERE clause. This function should be applied in the SELECT clause to format the retrieved dates for display purposes:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y') AS formatted_date
FROM table_name
WHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Additionally, it's important to consider the data type of the create_date column. If it is stored as a DATETIME with a non-zero time component, the above query will not accurately select today's records. In such cases, use NOW() instead:

SELECT DATE_FORMAT(create_date, '%m/%d/%Y') AS formatted_date
FROM table_name
WHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

By applying these corrections, the query will correctly retrieve records created within the specified 30-day range, formatted in the desired 'mm/dd/yy' format.

The above is the detailed content of How to Select MySQL Records from the Last 30 Days, Including Today?. For more information, please follow other related articles on the PHP Chinese website!