MySQL has a PERIOD_ADD() function that allows you to add a number of months to a given period. It returns a value in the format YYYYMM.

This article provides examples to demonstrate how it works.

Syntax

The syntax goes like this:

PERIOD_ADD(P,N)

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

Note that, although this function appears to be adding months to a date, the period argument is not actually a date value.

Example 1 – Basic Usage

Here’s a basic example.

SELECT PERIOD_ADD(202101,2);

Result:

+----------------------+
| PERIOD_ADD(202101,2) |
+----------------------+
|               202103 |
+----------------------+

So in this case, we added two months to the period.

Example 2 – Negative Values

Here’s an example that adds a negative number of months.

SELECT PERIOD_ADD(202101,-2);

Result:

+-----------------------+
| PERIOD_ADD(202101,-2) |
+-----------------------+
|                202011 |
+-----------------------+

Example 3 – Two Digit Years

This example uses a two digit year component.

SELECT PERIOD_ADD(2101,2);

Result:

+--------------------+
| PERIOD_ADD(2101,2) |
+--------------------+
|             202103 |
+--------------------+

You’ll notice that the result still uses a four digit year (even though we provided the period argument as a two digit year).

Example 4 – Using the Current Date

This example derives the period from the current date. It then adds a month on to that period.

SELECT 
    CURDATE( ) AS 'Current Date',
    EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Current Period',
    PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE( )), 1) AS 'Next Period';

Result:

+--------------+----------------+-------------+
| Current Date | Current Period | Next Period |
+--------------+----------------+-------------+
| 2018-06-30   |         201806 |      201807 |
+--------------+----------------+-------------+

Example 5 – A Database Example

Here’s an example that queries a database.

USE sakila;
SELECT
  payment_date AS 'Payment Date',
  EXTRACT(YEAR_MONTH FROM payment_date) AS 'Payment Period',
  PERIOD_ADD(EXTRACT(YEAR_MONTH FROM payment_date), 12) AS 'Next Payment'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+----------------+--------------+
| Payment Date        | Payment Period | Next Payment |
+---------------------+----------------+--------------+
| 2005-05-25 11:30:37 |         200505 |       200605 |
+---------------------+----------------+--------------+