MySQL Query - Records between Today and Last 30 Days

Question

Greetings! 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 SYSDATE instead:

SELECT  DATE_FORMAT(create_date, '%m/%d/%Y')
FROM    mytable
WHERE   create_date BETWEEN SYSDATE() - INTERVAL 30 DAY AND SYSDATE()

Answer 2

DATE_FORMAT returns a string, so you're using two strings in your BETWEEN clause, which isn't going to work as you expect.

Instead, convert the date to your format in the SELECT and do the BETWEEN for the actual dates. For example,

SELECT DATE_FORMAT(create_date, '%m/%d/%y') as create_date_formatted
FROM table
WHERE create_date BETWEEN CURDATE() AND (CURDATE() - INTERVAL 30 DAY)

Answer 3

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

Answer 4

thanks a bunch, it saves me to create a query like this....