How to Use period_add and period_diff Functions in MySQL

In MySQL, period_add adds a specified number of months to a period in the format YYYYMM, returning a new period. The period_diff function calculates the number of months between two periods in YYYYMM format.

Syntax

period_add(period, months): Adds months to the period (format YYYYMM) and returns the resulting period as YYYYMM.

period_diff(period1, period2): Returns the number of months between period1 and period2, both in YYYYMM format. The result is period1 - period2 in months.

mysql

SELECT PERIOD_ADD(202301, 2) AS added_period;
SELECT PERIOD_DIFF(202304, 202301) AS months_difference;

Output

added_period
----------
202303

months_difference
-----------------
3

Example

This example shows how to add 5 months to January 2022 and find the difference in months between December 2023 and March 2023.

mysql

SELECT PERIOD_ADD(202201, 5) AS new_period;
SELECT PERIOD_DIFF(202312, 202303) AS months_diff;

Output

new_period
----------
202206

months_diff
-----------
9

Common Pitfalls

• Passing periods not in YYYYMM format can cause incorrect results or errors.
• Remember that period_diff returns period1 - period2 in months, so order matters.
• Adding months that cross year boundaries works correctly, but be sure to use valid month values.

mysql

/* Wrong: period not in YYYYMM format */
SELECT PERIOD_ADD(2022, 3) AS wrong_period;

/* Right: use full YYYYMM format */
SELECT PERIOD_ADD(202201, 3) AS correct_period;

Output

wrong_period
------------
NULL

correct_period
--------------
202204

Quick Reference

Function	Description	Input Format	Output
period_add(period, months)	Adds months to a period	period: YYYYMM, months: integer	New period YYYYMM
period_diff(period1, period2)	Months difference between two periods	period1, period2: YYYYMM	Integer months (period1 - period2)

Key Takeaways

Use YYYYMM format for periods with period_add and period_diff functions.

period_add adds months to a period and returns a new YYYYMM period.

period_diff returns the number of months between two periods as period1 minus period2.

Order of arguments in period_diff affects the sign of the result.

Avoid passing incomplete or invalid period formats to prevent errors.