MySQL: date difference in months (even if 'month' < 30 days should be count as 1)

Question

I would like to compare 2 dates: '2012-05-05', '2012-06-04' and receive 1 as a result (difference bettwen May and June).

What I got:

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04') as difference
-- output: 0

I'm looking for a query for which I will receive 1 as a result (dates are from 2 different months; not important if difference is in fact < 30 days).

I've tried:

SELECT TIMESTAMPDIFF(MONTH, DATE_FORMAT('2012-05-05','%Y-%m'), DATE_FORMAT('2012-06-04','%Y-%m') ) as difference
-- output: NULL

also:

SELECT DATEDIFF( DATE_FORMAT('2012-05-05','%Y-%m'), DATE_FORMAT('2012-06-04','%Y-%m') ) as difference
-- output: NULL

Do you have other ideas?

score 2 · Accepted Answer · edited May 23 '17 at 12:32

2

I don't know if there are ways of doing it with the function, but you can do simple case... Obviously can be improved.

CASE
 WHEN DAYS <=30 THEN 1
 WHEN DAYS BETWEEN 31 and 60 THEN 2
 --ELSE ....
END as MONTH_DIFF

Also found this solution here:

SELECT 12 * (YEAR(DateOfService) 
              - YEAR(BirthDate)) 
       + (MONTH(DateOfService) 
           - MONTH(BirthDate)) AS months 
FROM table

edited May 23 '17 at 12:32

Community

1
1

answered Dec 10 '14 at 14:25

Andrew

7,619
13
63
117

1. Won't work (7 days can give result 0 or 1 depending on "real" month): `SELECT CASE WHEN DATEDIFF('2012-02-05', '2012-02-06') <=30 THEN 1 END as MONTH_DIFF -- Output: 1; Expected output: 0` 2. That's AWESOME! Thank's :) – suz Dec 10 '14 at 15:04

score 2 · Answer 2 · answered Jan 07 '21 at 04:29

In mysql documentation has function PERIOD_DIFF that returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM.

SELECT PERIOD_DIFF('201205', '201206') as difference

If you from datetime value, use DATE_FORMAT. Such as :

SELECT PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'),DATE_FORMAT(YOURCOLUMN, '%Y%m')) AS difference 
FROM YOURTABLE;

MySQL: date difference in months (even if 'month' < 30 days should be count as 1)

2 Answers2