MySQL has a PERIOD_ADD()
function that allows you to add a number of months to a given period. It returns a value in the formatĀ YYYYMM.
This article provides examples to demonstrate how it works.
Syntax
The syntax goes like this:
PERIOD_ADD(P,N)
Where P
is the period, and N
is the number of months to add.
Note that, although this function appears to be adding months to a date, the period argument is not actually a date value.
Example 1 – Basic Usage
Here’s a basic example.
SELECT PERIOD_ADD(202101,2);
Result:
+----------------------+ | PERIOD_ADD(202101,2) | +----------------------+ | 202103 | +----------------------+
So in this case, we added two months to the period.
Example 2 – Negative Values
Here’s an example that adds a negative number of months.
SELECT PERIOD_ADD(202101,-2);
Result:
+-----------------------+ | PERIOD_ADD(202101,-2) | +-----------------------+ | 202011 | +-----------------------+
Example 3 – Two Digit Years
This example uses a two digit year component.
SELECT PERIOD_ADD(2101,2);
Result:
+--------------------+ | PERIOD_ADD(2101,2) | +--------------------+ | 202103 | +--------------------+
You’ll notice that the result still uses a four digit year (even though we provided the period argument as a two digit year).
Example 4 – Using the Current Date
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';
Result:
+--------------+----------------+-------------+ | Current Date | Current Period | Next Period | +--------------+----------------+-------------+ | 2018-06-30 | 201806 | 201807 | +--------------+----------------+-------------+
Example 5 – A Database Example
Here’s an example that queries a database.
USE sakila; SELECT payment_date AS 'Payment Date', EXTRACT(YEAR_MONTH FROM payment_date) AS 'Payment Period', PERIOD_ADD(EXTRACT(YEAR_MONTH FROM payment_date), 12) AS 'Next Payment' FROM payment WHERE payment_id = 1;
Result:
+---------------------+----------------+--------------+ | Payment Date | Payment Period | Next Payment | +---------------------+----------------+--------------+ | 2005-05-25 11:30:37 | 200505 | 200605 | +---------------------+----------------+--------------+