MySQL Query - Records between Today and Last 30 Days
You need to apply DATE_FORMAT
in the SELECT
clause, not the WHERE
clause:
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')FROM mytableWHERE create_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
Also note that CURDATE()
returns only the DATE
portion of the date, so if you store create_date
as a DATETIME
with the time portion filled, this query will not select the today's records.
In this case, you'll need to use NOW
instead:
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')FROM mytableWHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
DATE_FORMAT
returns a string, so you're using two strings in your BETWEEN
clause, which isn't going to work as you expect.
Instead, convert the date to your format in the SELECT
and do the BETWEEN
for the actual dates. For example,
SELECT DATE_FORMAT(create_date, '%m/%d/%y') as create_date_formattedFROM tableWHERE create_date BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE()