How to show records for Last Month, This Month, Last Week, This Week, Last Year, This Year in MySQL

Just add these AND SQL statements to your queries, in these examples the ORDER_DATE is your column name which holds the date you are comparing against.

This Week

AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE)

Last Week

AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE – INTERVAL 7 DAY)

This Month

AND MONTH(ORDER_DATE) = MONTH(NOW()) AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

Last Month

AND SUBSTRING(ORDER_DATE FROM 1 FOR 7) = SUBSTRING(CURRENT_DATE – INTERVAL 1 month FROM 1 FOR 7)

or

AND MONTH(ORDER_DATE) = (MONTH(NOW())-1) AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

This Year

AND YEAR(ORDER_DATE) = YEAR(CURRENT_DATE())

Last Year

AND YEAR(ORDER_DATE) = (YEAR(CURRENT_DATE())-1)