NaD NaD - 3 years ago 110
MySQL Question

Query every between dates every month

For example: between 2017-08-29 and 2017-09-29
how do i do this every month not by defining a specific date

Ultimately my goal is to look like this

SELECT MONTH, SUM(SALES)
FROM STATUS
WHERE DATE BETWEEN 2017-"MONTH"-29 AND 2017-"MONTH"-29

Answer Source

Current month:

SELECT MONTH, SUM(SALES)
FROM STATUS
WHERE DATE > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
AND DATE < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)

Previous month:

SELECT MONTH, SUM(SALES)
FROM STATUS
WHERE DATE > LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
AND DATE < DATE_ADD(LAST_DAY(CURDATE() - INTERVAL 1 MONTH), INTERVAL 1 DAY)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download