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

How to Select Database Records Between Today and Last 30 Days with MySQL

This question seeks to retrieve records added within the last 30 days, with a required date conversion for display. The provided query attempts to use DATE_FORMAT within the WHERE clause, but it doesn't restrict the selection as intended.

Solution

The correct solution involves applying DATE_FORMAT in the SELECT clause:

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

Reason for Correction

MySQL's DATE_FORMAT function converts a date to a specific format, but it doesn't modify the underlying date value. Therefore, it should be applied after the WHERE clause, which specifies the date range of interest.

Additional Considerations

If create_date is stored as a DATETIME with a non-zero time component, using CURDATE() in the WHERE clause will not select today's records. To account for this, use NOW() instead:

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

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