MySQL PERIOD_ADD() Function

MySQL PERIOD_ADD() function; In this tutorial, i am going to show youMySQL period_add() function with the help of examples.

MySQL PERIOD_ADD() Function

The PERIOD_ADD() function adds a specified number of months to a period. The PERIOD_ADD() function will return the result formatted as YYYYMM.

Syntax of MySQL PERIOD_ADD() Function

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 – MySQL PERIOD_ADD() Function

Let’s take a simple example of this function; as follows:

SELECT PERIOD_ADD(201901,2);

Output-1

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

Example 2 – MySQL PERIOD_ADD() Function

Lets’s take an second example that adds a negative number of months in given period; as follows:

SELECT PERIOD_ADD(202101,-2);

Output-2

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

Example 3 – Add Year Using MySQL PERIOD_ADD() Function

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 – MySQL PERIOD_ADD() with CURDATE Function

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 |
+--------------+----------------+-------------+

Conclusion

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

Leave a Comment