MySQL Query - Records between Today and Last 30 Days
You can also write this in mysql -
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE create_date < DATE_ADD(NOW(), INTERVAL -1 MONTH);
FIXED
You need to apply DATE_FORMAT
in the SELECT
clause, not the WHERE
clause:
SELECT DATE_FORMAT(create_date, '%m/%d/%Y')
FROM mytable
WHERE 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 mytable
WHERE create_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
SELECT
*
FROM
< table_name >
WHERE
< date_field > BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)
AND NOW();