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 −

Result
202409

Example

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

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

Output

Following is the output −

Result
202211

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 −

ID NAME DATE_OF_BIRTH DOB_PERIOD COUNTRY
1 Shikhar Dhawan 1981-12-05 198112 India
2 Jonathan Trott 1981-04-22 198104 SouthAfrica
3 Kumara Sangakkara 1977-10-27 197710 Srilanka
4 Virat Kohli 1988-11-05 198811 India
5 Rohit Sharma 1987-04-30 198704 India
6 Ravindra Jadeja 1988-12-06 198812 India
7 James Anderson 1982-06-30 198206 England

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 −

ID NAME DATE_OF_BIRTH DOB_PERIOD Result
1 Shikhar Dhawan 1981-12-05 198112 198212
2 Jonathan Trott 1981-04-22 198104 198204
3 Kumara Sangakkara 1977-10-27 197710 197810
4 Virat Kohli 1988-11-05 198811 198911
5 Rohit Sharma 1987-04-30 198704 198804
6 Ravindra Jadeja 1988-12-06 198812 198912
7 James Anderson 1982-06-30 198206 198306
Advertisements