MySQL Query - Records between Today and Last 30 Days

Question

I want to return all records that were added to the database within the last 30 days. I need to convert the date to mm/dd/yy because of display purposes.

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

My statement fails to limit the records to the last 30 days - it selects all the records.

Can anyone point me in the right direction? It feels like I am close.

Thanks and have a great week.

Answer 1

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

Answer 2

SELECT     * FROM     < table_name > WHERE     < date_field > BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();