In MariaDB, PERIOD_DIFF() is a built-in date and time function that returns the number of months between two periods.

Syntax

The syntax goes like this:

PERIOD_DIFF(P1,P2)

Where P1 and P2 are the periods to get the difference between.

Both arguments are in the format YYMM or YYYYMM. They are not date values.

Example

Here’s an example:

SELECT PERIOD_DIFF(202108, 202101);

Result:

+-----------------------------+
| PERIOD_DIFF(202108, 202101) |
+-----------------------------+
|                           7 |
+-----------------------------+

In this example, there are seven months difference between the two periods.

Switch the Order

If the first period is an earlier period to the second, the result is a negative value.

Here’s what happens when I switch the order of the arguments in the previous example:

SELECT PERIOD_DIFF(202101, 202108);

Result:

+-----------------------------+
| PERIOD_DIFF(202101, 202108) |
+-----------------------------+
|                          -7 |
+-----------------------------+

Two-Digit Years

If any of the arguments contain a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.

Example:

SELECT 
    PERIOD_DIFF(6911, 6912),
    PERIOD_DIFF(6911, 7001);

Result:

+-------------------------+-------------------------+
| PERIOD_DIFF(6911, 6912) | PERIOD_DIFF(6911, 7001) |
+-------------------------+-------------------------+
|                      -1 |                    1198 |
+-------------------------+-------------------------+

Missing Argument

Calling PERIOD_DIFF() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT PERIOD_DIFF();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PERIOD_DIFF'

And another example:

SELECT PERIOD_DIFF( 6912 );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'PERIOD_DIFF'