Calculate date difference in months when format is Month YYYY

Question

I have stored a date in two columns like :

from          |   to
----------------------
January 2012  | March 2013    
July 2011     | June 2013

How can I get the difference in between these two dates in months?

I want the output as 15 for first row and 11 for second row

That's not really a good way to store a date when you want to do calculations with it. Why not use `DATETIME`? — Pekka, Apr 10 '13 at 07:56
@JW Because he can't count without a query :) He probably means from July **2012** and the count should be **12** — nl-x, Apr 10 '13 at 08:12

score 3 · Accepted Answer · answered Apr 10 '13 at 08:27

You should use function str_to_date() to convert your strings to actual DATE type. After that, you can use period_diff() to compute difference in months, but you have to convert your date using date_format() into format that is supported by period_diff (which is confusingly is NOT a date).

Final SQL is (SQLFiddle Demo):

SELECT period_diff(
    date_format(str_to_date(`to`,   '%M %Y'), '%Y%m'),
    date_format(str_to_date(`from`, '%M %Y'), '%Y%m'))
FROM mytable

One note though: I don't quite understand where did you get 15 and 11 from. This query gives difference between from and to in months, and for your data it is 14 and 23 respectively. Just to demonstrate, I have also added current month as from and to, and it does indeed compute to 0 difference.

Calculate date difference in months when format is Month YYYY

1 Answers1