MySQL Query - Between Today and the Last 30 Days

Question

I want to return all records that have been added to the database in the last 30 days. I need to convert date to mm / dd / yy due to display.

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

My operator cannot limit the records for the last 30 days - he selects all the records.

Can someone point me in the right direction? It seems to me that I'm close.

Thank you, I have a great week.

Answer 1

You need to apply DATE_FORMAT in the SELECT , and not in the WHERE :

 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 a portion of the DATE date, so if you save create_date as a DATETIME with a filled part of the time, this query will not select today's records.

In this case, 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(); 

Answer 3

DATE_FORMAT returns a string, so you use two strings in your BETWEEN clause, which will not work as you expect.

Instead, convert the date to your format in SELECT and do 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() - INTERVAL 30 DAY) AND CURDATE() 

Answer 4

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

Answer 5

For current activity and full activity for the previous 30 days, use this, since SYSDATE is a variable for one day, on the previous 30th day there will not be all the data for that day.

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

Answer 6

I would do something like this:

 SELECT DATE_FORMAT(create_date, '%m/%d/%Y') FROM mytable WHERE create_date >= (CURDATE() - INTERVAL 30 DAY)