How PERIOD_ADD() Works in MariaDB

In MariaDB, PERIOD_ADD() is a built-in date and time function that adds a given number of months to a period in time.

The return value is in the format YYYYMM.

Syntax

The syntax goes like this:

PERIOD_ADD(P,N)

Where P is the period and N is the number of months to add to it.

P is in the format YYMM or YYYYMM, and is not a date value.

Example

Here’s an example:

SELECT PERIOD_ADD(202011, 1);

Result:

+-----------------------+
| PERIOD_ADD(202011, 1) |
+-----------------------+
|                202012 |
+-----------------------+

That example added one month to the period.

Here’s one that adds six months to the period:

SELECT PERIOD_ADD(202011, 6);

Result:

+-----------------------+
| PERIOD_ADD(202011, 6) |
+-----------------------+
|                202105 |
+-----------------------+

Two-Digit Years

If the first argument contains 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_ADD(6910, 2),
    PERIOD_ADD(7010, 2);

Result:

+---------------------+---------------------+
| PERIOD_ADD(6910, 2) | PERIOD_ADD(7010, 2) |
+---------------------+---------------------+
|              206912 |              197012 |
+---------------------+---------------------+

The first one returned a year of 2069 and the second one 1970.

Missing Argument

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

SELECT PERIOD_ADD();

Result:

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

And another example:

SELECT PERIOD_ADD( 2010 );

Result:

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