Q . MySQL Query - Records between Today and Last 30 Days.

Brad Johnson. asked, Jan 15 ' 2016

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.

  • MySQL
  • between
  • resultset

1 Answers

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

 

Leave a Ansewer