MySQL: Selecting all records for the previous month.

I had a to do a report for a client today that would get all the orders placed in the previous month and that report needs to run on the 5-th of the following month. For example, if today was 5th of March it would have to show all the orders for the month of Februrary. The problem is that each month is different 28, 29, 30, or 31 days and since I couldn’t find a quick reference by googling here is my query (stripped of all specifics) for anyone who needs to run a similar query. The trick is to use

LAST_DAY()

function in MySQL.

select *
from 	orders
where order_date
between date_format(date_sub(curdate(), interval 1 month), "%Y-%m-01")
and date_format(last_day(date_sub(curdate(), interval 1 month)), "%Y-%m-%d")
order by order_date asc;

P.S.: My order_date is a date field in the format 2009-01-01, check DATE_FORMAT() function in MySQL spec to match your date field format.

Enjoy