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

User · Answer

Here s a solution without using curdate   function  this is a solution for those who use TSQL I guess  SELECT myDate FROM myTable WHERE myDate BETWEEN DATEADD DAY  -30  GETDATE    AND GETDATE

User · Answer

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   - INTERVAL 30 DAY  AND CURDATE

User · Answer

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

User · Answer

You can also write this in mysql -   SELECT  DATE FORMAT create date    m  d  Y   FROM    mytable WHERE   create date  lt  DATE ADD NOW    INTERVAL -1 MONTH     FIXED

User · Answer

For the current date activity and complete activity for previous 30 days use this  since the SYSDATE is variable in a day the previous 30th day will not have the whole data for that day   SELECT  DATE FORMAT create date    m  d  Y   FROM mytable WHERE create date BETWEEN CURDATE   - INTERVAL 30 DAY AND SYSDATE

User · Answer

SELECT       FROM      lt  table name  gt  WHERE      lt  date field  gt  BETWEEN DATE SUB NOW    INTERVAL 30 DAY  AND NOW

[mysql] MySQL Query - Records between Today and Last 30 Days

Examples related to mysql

Examples related to date

Examples related to select