MySQL PERIOD_ADD() Function

Summary: in this tutorial, you will learn how to use the MySQL PERIOD_ADD() function to add a specified number of months to a period.

Introduction to MySQL PERIOD_ADD() function

The PERIOD_ADD() function allows you to add a number of months to a period in the format YYMM or YYYMMM.

Here’s the syntax of the PERIOD_ADD() function:

PERIOD_ADD(P, N)Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • P: The period to which you want to add months (in the format YYMM or YYYYMM).
  • N: The number of months to add to the period.

The PERIOD_ADD() function returns a value in the same format as P, which is YYMM or YYYYMM.

It returns NULL if either P or N is NULL.

MySQL PERIOD_ADD() function examples

Let’s take some examples of using the PERIOD_ADD() function.

1) Using PERIOD_ADD() to add months to a period example

The following example adds 2 months to the period 201310:

SELECT PERIOD_ADD(201310, 2);Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+
| PERIOD_ADD(201310, 2) |
+-----------------------+
|                201312 |
+-----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using PERIOD_ADD() function with NULL values example

The PERIOD_ADD() function returns NULL if either period (P) or the number of months (N) is NULL:

SELECT PERIOD_ADD(NULL, 2);Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| PERIOD_ADD(NULL, 2) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

And:

SELECT PERIOD_ADD(202312, NULL);Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------+
| PERIOD_ADD(202312, NULL) |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

Summary

  • Use the PERIOD_ADD() function to add a number of months to a period in the format YYMM or YYYYMM.
Was this tutorial helpful?