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)

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

Leave a Reply

Your email address will not be published. Required fields are marked *

50 + = fifty four