Query to get all rows from previous month

user_250178 0
Viewed 185232

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.

mysql
13 Answers
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
user_1056620

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 >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' ) 
AND
    date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
user_1831720

Alternative with single condition

SELECT * FROM table
WHERE YEAR(date_created) * 12 + MONTH(date_created)
    = YEAR(CURRENT_DATE) * 12 + MONTH(CURRENT_DATE) - 1
user_10649010

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_2467620

One more way to do this in:

MYSQL

select * from <table_name> where date_created >= DATE_ADD(NOW(), INTERVAL -30 DAY);
user_56346030

if you want to get orders from last month, you can try using

WHERE MONTH(order_date) = MONTH(CURRENT_DATE()) -1
user_140524770 
SELECT *  FROM table  
WHERE  YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
user_110247560
Related