Reputation: 3794
I have created Query which gets me the previous 12-month data which contain current month. I want the 12 months from the previous month. I don't get that, so can anyone help me over this.
create_data >= DATE_SUB(CURDATE(), INTERVAL 12 month)
Upvotes: 0
Views: 75
Reputation: 142560
WHERE create_date >= CURDATE() - INTERVAL DAY(CURDATE() -1) DAY
- INTERVAL 12 MONTH
AND create_date < CURDATE() - INTERVAL DAY(CURDATE() -1) DAY
Backing up by DAY(CURDATE())
gets to the end of the previous month.
The -1
is to shift that to the first of the month.
- INTERVAL 12 MONTH
gets back 12 months.
The use of >=
and <
prevents getting an extra day or second on the end.
To check:
SELECT NOW(),
CURDATE() - INTERVAL DAY(CURDATE() -1) DAY
- INTERVAL 12 MONTH AS start,
CURDATE() - INTERVAL DAY(CURDATE() -1) DAY
+---------------------+------------+------------+
| NOW() | start | end |
+---------------------+------------+------------+
| 2017-02-27 15:31:09 | 2016-02-01 | 2017-02-01 |
+---------------------+------------+------------+
Upvotes: 1
Reputation: 11205
For the 12 months up to last month:
create data between DATE_SUB(CURDATE(), INTERVAL 13 month)
and DATE_SUB(CURDATE(), INTERVAL 1 month)
Upvotes: 1