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();

Tags:

Mysql

Date

Select