MySQL PERIOD_ADD() Function

The PERIOD_ADD() function is used to add a specified number of months to a given period. The result is returned in the YYYYMM format.

Definition and Usage

• Purpose: Adds a specified number of months to a period, formatted as YYYYMM.
• Output Format: The result is returned in YYYYMM format (Year and Month).

Syntax

PERIOD_ADD(period, number)

Parameters

1. period: The period to which months will be added. It must be in the format YYMM or YYYYMM.
2. number: The number of months to add. It can be a positive or negative integer:
   • Positive values will add months.
   • Negative values will subtract months.

Technical Details

• Works in: MySQL 4.0 and later.
• Return Type: A period in the format YYYYMM.

Examples

Example 1: Add 5 Months to a Period

SELECT PERIOD_ADD(201703, 5);

Result: 201808
Explanation: The period 201703 represents March 2017, and adding 5 months results in August 2017 (201708).

Example 2: Add 15 Months to a Period

SELECT PERIOD_ADD(201703, 15);

Result: 201806
Explanation: Adding 15 months to March 2017 (201703) results in June 2018 (201806).

Example 3: Subtract 2 Months from a Period

SELECT PERIOD_ADD(201703, -2);

Result: 201701
Explanation: Subtracting 2 months from March 2017 (201703) results in January 2017 (201701).

Use Cases

• Date Calculations: Use this function when you need to perform period-based calculations, such as adding or subtracting months for subscription periods, project timelines, or financial forecasting.
• Date Adjustments in Reports: For generating reports that require month-based adjustments or transformations, like comparing months over multiple periods.

Key Notes

• The function works with the period format YYYYMM and is useful for operations involving months rather than exact dates.
• Both positive and negative values can be used to perform additions or subtractions in months.