MySQL Query - Records between Today and Last 30 Days

mysql date select
Jason Sweet picture Jason Sweet · Jan 11, 2010 · Viewed 259.7k times · Source

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

Quassnoi picture Quassnoi · Jan 11, 2010

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

Related questions

MySQL SELECT WHERE datetime matches day (and not necessarily time)

I have a table which contains a datetime column. I wish to return all records of a given day regardless of the time. Or in other words, if my table only contained the following 4 records, then only the 2nd and 3…

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More