Calculating how many months and years are there between 2009-12-26 to now in mysql

Question

In mySql how can I calculate the months and years from a 'given date' till now. That 'given date' will be selected from a table. Or is this something I'll have to use php to do?

So the sql will be select min(datecol) from table where userid = 12;

I came across this here select DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), '2009-12-26')), '%c months and %Y years'); but this gives 1 months and 0003 years

Three years is ok, but 1 month is wrong. It should be 36+ months in this case.

possible duplicate of http://stackoverflow.com/questions/1383516/date-difference-in-mysql-to-calculate-age — Mevin Babu, Dec 27 '12 at 03:45
That question gives you the years, but how do you do the months? — Norman, Dec 27 '12 at 04:03

score 0 · Accepted Answer · answered Dec 27 '12 at 04:15

To find month

select 
  period_diff(date_format(now(), '%Y%m'), date_format(`date_field`, '%Y%m')) 
  as months from `your_table`

or this

 SELECT
        12 * (YEAR(now()) - YEAR('2012-02-26')) + 
        (MONTH(now()) - MONTH('2012-02-26')) AS months

refer the Mevin Babu link to find year

Calculating how many months and years are there between 2009-12-26 to now in mysql

1 Answers1