SQL Tutorial SQL Advanced SQL Database SQL References

MySQL PERIOD_ADD() Function



The MySQL PERIOD_ADD() function adds a specified number of months to a period. It takes period formatted as YYMM or YYYYMM and returns the result formatted as YYYYMM.

Syntax

PERIOD_ADD(period, number)

Parameters

period Required. Specify the period formatted as either YYMM or YYYYMM.
number Required. Specify the number of months to add to the period. It can be a positive or negative value.

Return Value

Returns the period after adding specified number of months.

Example 1:

The example below shows the usage of PERIOD_ADD() function.

mysql> SELECT PERIOD_ADD(201808, 4);
Result: 201812

mysql> SELECT PERIOD_ADD(201808, -4);
Result: 201804

mysql> SELECT PERIOD_ADD(201808, 9);
Result: 201905

mysql> SELECT PERIOD_ADD(201808, -9);
Result: 201711

mysql> SELECT PERIOD_ADD(1808, 4);
Result: 201812

mysql> SELECT PERIOD_ADD(1808, -4);
Result: 201804

mysql> SELECT PERIOD_ADD(1808, 9);
Result: 201905

mysql> SELECT PERIOD_ADD(1808, -9);
Result: 201711

Example 2:

Consider a database table called Sample with the following records:

DataPeriodMonth
Data 12014046
Data 22015047
Data 32016048
Data 42017049
Data 520180410

To create a period based on adding values of column Period and column Month, the following query can be used:

SELECT *, PERIOD_ADD(Period, Month) AS PERIOD_ADD_Value FROM Sample;

This will produce the result as shown below:

DataPeriodMonthPERIOD_ADD_Value
Data 12014046201410
Data 22015047201511
Data 32016048201612
Data 42017049201801
Data 520180410201902

❮ MySQL Functions

5