PERIOD_ADD() Function Examples – MySQL

PERIOD_ADD() Function Examples – MySQL

In this MySQL tutorial – we would love to share with you how to works MySQL period_add() function with 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.

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *