计算2009-12-26到现在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.

Answer 1

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