Query to get all rows from previous month

Question

I need to select all rows in my database that were created last month    For example  if the current month is January  then I want to return all rows that were created in December  if the month is February  then I want to return all rows that were created in January  I have a date created column in my database that lists the date created in this format  2007-06-05 14 50 17

User · Answer

Alternatively to hobodave s answer  SELECT   FROM table WHERE YEAR date created    YEAR CURRENT DATE - INTERVAL 1 MONTH  AND MONTH date created    MONTH CURRENT DATE - INTERVAL 1 MONTH    You could achieve the same with EXTRACT  using YEAR MONTH as unit  thus you wouldn t need the AND  like so   SELECT   FROM table WHERE EXTRACT YEAR MONTH FROM date created    EXTRACT YEAR MONTH FROM CURDATE   - INTERVAL 1 MONTH

User · Answer

Here s another alternative   Assuming you have an indexed DATE or DATETIME type field  this should use the index as the formatted dates will be type converted before the index is used   You should then see a range query rather than an index query when viewed with EXPLAIN   SELECT        FROM     table WHERE      date created  gt   DATE FORMAT  CURRENT DATE - INTERVAL 1 MONTH    Y  m 01     AND     date created  lt  DATE FORMAT  CURRENT DATE    Y  m 01

User · Answer

WHERE created date  gt   DATE ADD LAST DAY DATE SUB NOW    INTERVAL 2 MONTH    INTERVAL 1 DAY     AND created date  lt   DATE ADD LAST DAY DATE SUB NOW    INTERVAL 1 MONTH    INTERVAL 0 DAY     This worked for me  Selects all records created from last month  regardless of the day you run the query this month

User · Answer

select fields FROM table WHERE date created LIKE concat LEFT DATE SUB NOW    interval 1 month  7         this one will be able to take advantage of an index if your date created is indexed  because it doesn t apply any transformation function to the field value

User · Answer

SELECT   FROM table WHERE YEAR date created    YEAR CURRENT DATE - INTERVAL 1 MONTH  AND MONTH date created    MONTH CURRENT DATE - INTERVAL 1 MONTH

User · Answer

If there are no future dates      SELECT    FROM   table name  WHERE  date created  gt   NOW   - INTERVAL 1 MONTH     Tested

User · Answer

Alternative with single condition SELECT   FROM table WHERE YEAR date created    12   MONTH date created        YEAR CURRENT DATE    12   MONTH CURRENT DATE  - 1

User · Answer

SELECT   FROM  yourtable where DATE FORMAT date created    Y- m     date format DATE SUB curdate    INTERVAL 1 month    Y- m     This should return all the records from the previous calendar month  as opposed to the records for the last 30 or 31 days

User · Answer

SELECT    FROM table   WHERE  YEAR date created    YEAR CURRENT DATE - INTERVAL 1 MONTH  AND MONTH date created    MONTH CURRENT DATE - INTERVAL 1 MONTH

User · Answer

Here is the query to get the records of the last month   SELECT   FROM  tablename  WHERE  datefiled  BETWEEN DATE SUB  DATE  NOW        INTERVAL 1 MONTH   AND  LAST DAY  DATE SUB  DATE  NOW        INTERVAL 1 MONTH       Regards - saqib

User · Answer

Even though the answer for this question has been selected already  however  I believe the simplest query will be  SELECT    FROM table  WHERE  date created BETWEEN  CURRENT DATE   - INTERVAL 1 MONTH  AND CURRENT DATE

[mysql] Query to get all rows from previous month

Examples related to mysql