Name
PERIOD_DIFF()
Synopsis
PERIOD_DIFF(yearmonth
,yearmonth
)
This function returns the number of months between the
periods given. The periods given must be in string format and contain
only the year and month, in either yyyymm
or yymm
format. Here is an example:
SELECT appointment AS 'Date of Appointment', CURDATE( ) AS 'Current Date', PERIOD_DIFF( EXTRACT(YEAR_MONTH FROM appointment), EXTRACT(YEAR_MONTH FROM CURDATE( )) ) AS 'Accounting Periods Apart'; +------------------+--------------+-----------------------------+ | Last Appointment | Current Date | Accounting Periods Ellapsed | +------------------+--------------+-----------------------------+ | 2008-11-15 | 2009-01-15 | -2 | +------------------+--------------+-----------------------------+
This SQL statement determines that it has been two months since
the client’s last appointment. If you want the results not to contain
a negative, either switch the order of the periods or wrap the
PERIOD_DIFF()
within
ABS()
. The
PERIOD_DIFF()
function takes into account
that the periods are in different years. But it doesn’t work on
standard date columns, so you have to put them into the proper string
format as shown here with a function such as
EXTRACT()
.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.