MySQL in a Nutshell

Name

PERIOD_DIFF( )

Synopsis

PERIOD_DIFF(yearmonth,yearmonth)

This function returns the number of months between periods. The dates must be in string format and contain only the year and month, in either yyyymm or yymm format.

SELECT PERIOD_DIFF(200403, 200401)
       AS 'Accounting Periods Apart';
+--------------------------+
| Accounting Periods Apart |
+--------------------------+
|                        2 |
+--------------------------+

The first period is for March 2004 and the second is for January 2004. This function doesn't work on standard date columns unless you put them into the format shown here. An example of doing this conversion is:

SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE( )),
       EXTRACT(YEAR_MONTH FROM appointment))
       AS 'Accounting Periods Apart'
   FROM appointments
   WHERE client_id = '5620'
   ORDER BY appointment DESC;
+--------------------------+
| Accounting Periods Apart |
+--------------------------+
|                       -2 |
+--------------------------+

This SQL statement determines that it's been two months since this client's last appointment. This works, but it's cumbersome.