Search code examples
mysqlsqldatediff

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

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?

Solution

  • 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