MySQL PERIOD_ADD() Function Example

MySQL PERIOD_ADD() function; In this tutorial, we would love to share with you how to use MySQL period_add() function with the help of examples.

Here, you will learn how to add specific months in the given period, also learn how to use this query with database tables.

MySQL PERIOD_ADD() Function

In MySQL, PERIOD_ADD () function that allows you to add several months to a certain period. It returns a value in YYYYMM format.

Syntax

The syntax of the MySQL PERIOD_ADD() function is below:

PERIOD_ADD(Period,Num)

Here: Period is the period, and Num is the number of months to add.

Example-1

Let’s take a simple example of this function. The example is below:

SELECT PERIOD_ADD(201901,2);

Output-1

+----------------------+
| PERIOD_ADD(201901,3) |
+----------------------+
|               201904 |
+----------------------+

We added three months to the given period.

Example-2

Lets’s take an example that adds a negative number of months in given period.

SELECT PERIOD_ADD(202101,-2);

Output-2

+-----------------------+
| PERIOD_ADD(201905,-1) |
+-----------------------+
|                201904 |
+-----------------------+

Example 3 – Two Digit Years

This example uses the component of the only two-digit year. Here first two number is a year and second to number is a month, after common that the number of periods adds to the given period.

SELECT PERIOD_ADD(1901,2);

Output-3

+--------------------+
| PERIOD_ADD(1901,2) |
+--------------------+
|             201903 |
+--------------------+

Here, you will notice that the given result by this query is a year in four digits.

Example-4

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';

Output-4

+--------------+----------------+-------------+
| Current Date | Current Period | Next Period |
+--------------+----------------+-------------+
| 2019-07-21   |         201907 |      201908 |
+--------------+----------------+-------------+

Example-5

Now we take another example with MySQL database.

  SELECT
  payment_date AS 'Payment Date',
  EXTRACT(YEAR_MONTH FROM payment_date) AS 'Payment Period',
  PERIOD_ADD(EXTRACT(YEAR_MONTH FROM payment_date), 6) AS 'Next Payment'
  FROM payments
  WHERE id = 1;

Output-5

+---------------------+----------------+--------------+
| Payment Date        | Payment Period | Next Payment |
+---------------------+----------------+--------------+
| 2018-01-10 12:50:37 |         201801 |       201806 |
+---------------------+----------------+--------------+

Conclusion

Here, you have learned how to use MySQL PERIOD_ADD() function with various examples.