MySQL PERIOD_ADD() Function – Easy Practical Examples

By /
PERIOD ADD Function

In this tutorial, we will study the MySQL PERIOD_ADD() function. Suppose you have a table in which you store the details of the invoices you have to pay- Id, Name of the beneficiary, the amount to be paid, and the period in which the amount should be paid. Now due to Covid-19, all these beneficiaries give you an extension of 6 months to pay these bills. You have to update the Payment period column now. This is where we use the PERIOD_ADD() function.

The MySQL PERIOD_ADD() function is used to add a specified number of months to a period. A period is a value of the format YYYYMM or YYMM where the Ys stand for the year value and Ms stand for the month value. When we have two Ys, then the last two digits of the year are used. An example of period values is 202104. Here 2021 is the year and 04 is the month. In the YYMM format, this will be written as 2104. So, if you want to add 6 months to the period 202104 then, by using PERIOD_ADD(), you get 202110.

Let us see the syntax and examples of the PERIOD_ADD() function now.

Syntax of MySQL PERIOD_ADD()

PERIOD_ADD(period, number)Code language: SQL (Structured Query Language) (sql)

Where ‘period’ is a period value of the format YYYYMM or YYMM and,

‘Number’ is the number of months to be added to the ‘period’.

Examples of MySQL PERIOD_ADD()

Let us start with some basic examples. We will use the SELECT statement and an alias called ‘Period’ to make our output readable. Let us write two queries. The first one will add 2 months to the period 202010 and the second query will add 8 months to the period 201901. The queries are –

SELECT PERIOD_ADD(202010, 2) AS Period; 
SELECT PERIOD_ADD(201901, 8) AS Period;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL PERIOD_ADD Basic Example

Now, what if you have the period value – 202010 and you add 5 months to it. MySQL PERIOD_ADD() is smart enough to increment the year value in such a case and add the months in the new year. Let us see an example of this. Consider the query –

SELECT PERIOD_ADD(202010, 5) AS Period; Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL PERIOD_ADD Wrap Around

MySQL PERIOD_ADD() With Negative ‘number’ Value

What if we pass a negative value as the ‘number’ parameter? Let us see a couple of examples of this. Consider the below queries.

SELECT PERIOD_ADD(201905, -4) AS Period; 
SELECT PERIOD_ADD(202012, -2) AS Period;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL PERIOD_ADD Negative Values

As you can see, since we tried to add a negative month value to the period, PERIOD_ADD in turn, subtracted those many months from the given period.

MySQL PERIOD_ADD() With Two Digit Year Value

As I mentioned in the syntax, we can also pass the period value in the YYMM format to PERIOD_ADD(). However, it is important to note that the output will be in the format YYYYMM. Let us see a couple of examples of this. Consider the below queries.

SELECT PERIOD_ADD(2010, 2) AS Period; 
SELECT PERIOD_ADD(1905, 6) AS Period;Code language: SQL (Structured Query Language) (sql)

And the output is –

Period Add Two Digit Years

Working With Tables

Let us now see an example of the PERIOD_ADD() function with tables. Consider the below ‘Invoices’ table.

Period Add Invoices Table
Invoices Table

Now let us come back to the problem I mentioned in the beginning of this article. Suppose you have an Invoices table in which you store the details of the invoices you have to pay- Id, Name of the beneficiary, the amount to be paid and the period in which the amount should be paid. Now due to Covid-19, all these beneficiaries give you an extension of 6 months to pay these bills. You have to update the Payment period column now.

Now that we know about the PERIOD_ADD() function, this should be easy. We will also use the UPDATE statement. Consider the below queries.

UPDATE Invoices SET PaymentPeriod=PERIOD_ADD(PaymentPeriod, 6); 
SELECT * FROM Invoices;Code language: SQL (Structured Query Language) (sql)

And the output is –

Period Add Table Example

Conclusion

Addition operations on period values are very important and have a plethora of use cases. That is why, the PERIOD_ADD() function is so important. I would encourage you to practice some more queries with this function.

References