CODEWITHSUNDEEP
phpmysqlsqlinnodb
always-a-learner
always-a-learner

Reputation: 3794

get previous 12 month from sql

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

Answers (2)

Rick James
Rick James

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

JohnHC
JohnHC

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

Related Questions