SQL Tutorial SQL Advanced SQL Database SQL References

MySQL PERIOD_DIFF() Function



The MySQL PERIOD_DIFF() function returns the difference between two periods. The result will be in months. The parameters period1 and period2 must be formatted as either YYMM or YYYYMM, but do not have to be the same format as each other.

Syntax

PERIOD_DIFF(period1, period2)

Parameters

period1 Required. Specify the first period formatted as either YYMM or YYYYMM.
period2 Required. Specify the second period formatted as either YYMM or YYYYMM.

Return Value

Returns the difference between two periods.

Example 1:

The example below shows the usage of PERIOD_DIFF() function.

mysql> SELECT PERIOD_DIFF(201805, 201801);
Result: 4

mysql> SELECT PERIOD_DIFF(201801, 201805);
Result: -4

mysql> SELECT PERIOD_DIFF(201805, 1901);
Result: -8

mysql> SELECT PERIOD_DIFF(1901, 201805);
Result: 8

mysql> SELECT PERIOD_DIFF(1706, 1901);
Result: -19

mysql> SELECT PERIOD_DIFF(1901, 1706);
Result: 19

Example 2:

Consider a database table called Sample with the following records:

DataPeriod1Period2
Data 1201404201410
Data 2201505201508
Data 3201606201606
Data 4201707201704
Data 5201808201802

To calculate the difference between periods specified by values of column Period1 and column Period2, the following query can be used:

SELECT *, PERIOD_DIFF(Period1, Period2) AS PERIOD_DIFF_Value FROM Sample;

This will produce the result as shown below:

DataPeriod1Period2PERIOD_DIFF_Value
Data 1201404201410-6
Data 2201505201508-3
Data 32016062016060
Data 42017072017043
Data 52018082018026

❮ MySQL Functions