The PERIOD_DIFF() function calculates the difference in months between two periods. Both periods must be in the same format (YYMM or YYYYMM), and the result is returned as the number of months between them.
Definition and Usage
- Purpose: Calculates the difference between two periods and returns the result in months.
- Output: The number of months between the two periods.
Syntax
PERIOD_DIFF(period1, period2)
Parameters
- period1: The first period in
YYMMorYYYYMMformat. - period2: The second period in
YYMMorYYYYMMformat.
Both periods should be in the same format, and the result will represent the difference in months between the two periods.
Technical Details
- Works in: MySQL 4.0 and later.
- Return Type: Integer (the number of months between the two periods).
Examples
Example 1: Calculate the Difference Between Two Periods
SELECT PERIOD_DIFF(201710, 201703);
Result: 7
Explanation: The difference between October 2017 (201710) and March 2017 (201703) is 7 months.
Example 2: Calculate the Difference Between Two Periods (with negative result)
SELECT PERIOD_DIFF(201703, 201803);
Result: -12
Explanation: The difference between March 2017 (201703) and March 2018 (201803) is -12 months (since March 2018 is 12 months after March 2017).
Example 3: Calculate the Difference Between Two Periods (using YYMM format)
SELECT PERIOD_DIFF(1703, 1612);
Result: 9
Explanation: The difference between March 2017 (1703) and December 2016 (1612) is 9 months.
Use Cases
- Calculating Month Differences: Useful for determining the number of months between two dates represented as periods, such as calculating time intervals between financial quarters or subscription periods.
- Comparing Periods: Ideal for comparing different time periods (like months or years) in reports, forecasts, or time-based analysis.
Key Notes
- The function returns the difference in months, and the result can be positive or negative depending on the order of the periods.
- Both periods must be in the same format (
YYMMorYYYYMM), or the function will return an error.