MySQL - PERIOD_ADD() Function

The MySQL PERIOD_ADD() function is used to add a certain number of months to a period. In MySQL, a period is defined as a combination of year and month; and it will be represented in the format, YYMM or YYYYMM.

This MySQL function accepts a period and a numerical value representing the number of months as its arguments; and adds the given number of months to the given period. The result of this function will be in the form of YYYYMM.

Syntax

Following is the syntax of MySQL PERIOD_ADD() function −

PERIOD_ADD(P,N);

Parameters

This method accepts two parameters. The same is described below −

• P is the value period value. The format is "YYMM" or "YYYYMM"

• N is the number of months you need to add to the period.

Return value

This function returns a new period value after adding the specified number of months.

Example

Following example demonstrates the usage of the PERIOD_ADD() function −

SELECT PERIOD_ADD('202309', 12) As Result;

Output

This will produce the following result −

Example

We can also pass negative values as arguments to this function −

SELECT PERIOD_ADD('202308', -09) As Result;

Output

Following is the output −

Example

In this example, we have created a table named PLAYERS using the following CREATE TABLE query

CREATE TABLE PLAYERS(
   ID int,
   NAME varchar(255),
   DATE_OF_BIRTH date,
   DOB_PERIOD varchar(30),
   Country varchar(255),
   PRIMARY KEY (ID)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar Dhawan', DATE('1981-12-05'), 198112, 'India'),
(2, 'Jonathan Trott', DATE('1981-04-22'), 198104, 'SouthAfrica'),
(3, 'Kumara Sangakkara', DATE('1977-10-27'), 197710, 'Srilanka'),
(4, 'Virat Kohli', DATE('1988-11-05'), 198811, 'India'),
(5, 'Rohit Sharma', DATE('1987-04-30'), 198704, 'India'),
(6, 'Ravindra Jadeja', DATE('1988-12-06'), 198812, 'India'),
(7, 'James Anderson', DATE('1982-06-30'), 198206, 'England');

Execute the below query to fetch all the inserted records in the above-created table −

Select * From PLAYERS;

Following is the PLAYERS table −

Here, we are using the MySQL PERIOD_ADD() function to add 12 months to "DOB_PERIOD" from the "PLAYERS" table −

SELECT ID, NAME, DATE_OF_BIRTH, DOB_PERIOD, PERIOD_ADD(DOB_PERIOD, 12)
As Result From PLAYERS;

Output

The output is displayed as follows −