How to use the MySQL PERIOD_ADD() function

In this article, we will learn how to use the MySQL PERIOD_ADD() function, which adds a number of months to a period and returns the resulting period.

Posted on

In this article, we will learn how to use the MySQL PERIOD_ADD() function, which adds a number of months to a period and returns the resulting period. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with periods.

Syntax

The syntax of the PERIOD_ADD() function is as follows:

PERIOD_ADD(period, number)

The period parameter can be any valid period expression, which is a four or six-digit number that represents a year and a month. For example, 202312 represents the period of December 2023, and 202301 represents the period of January 2023. The number parameter can be any integer value that represents the number of months to be added to the period. If the number parameter is positive, the function adds the number of months to the period. If the number parameter is negative, the function subtracts the number of months from the period. If either parameter is NULL, the function returns NULL.

Examples

Let’s see some examples of how to use the PERIOD_ADD() function in MySQL.

Example 1: Add a number of months to a period

We can use the PERIOD_ADD() function to add a number of months to a period and get the resulting period. For example:

SELECT PERIOD_ADD(202312, 3) AS result;

This query will add 3 months to the period of December 2023 and return the resulting period. The query will return 202403, which represents the period of March 2024.

Example 2: Subtract a number of months from a period

We can use the PERIOD_ADD() function with a negative number to subtract a number of months from a period and get the resulting period. For example:

SELECT PERIOD_ADD(202301, -2) AS result;

This query will subtract 2 months from the period of January 2023 and return the resulting period. The query will return 202211, which represents the period of November 2022.

Example 3: Get the difference between two periods

We can use the PERIOD_ADD() function with the PERIOD_DIFF() function, which returns the number of months between two periods, to get the difference between two periods. For example, suppose we want to get the difference between the periods of June 2023 and March 2022. We can use the following query:

SELECT PERIOD_ADD(202206, -PERIOD_DIFF(202206, 202203)) AS result;

This query will subtract the number of months between the periods of June 2023 and March 2022 from the period of June 2023 and return the resulting period. The query will return 202203, which represents the period of March 2022.

Example 4: Get the period of the current month

We can use the PERIOD_ADD() function with the YEAR() and MONTH() functions, which return the year and month of a date or datetime value, respectively, to get the period of the current month. For example:

SELECT PERIOD_ADD(YEAR(CURDATE()) * 100 + MONTH(CURDATE()), 0) AS result;

This query will get the year and month of the current date, multiply the year by 100 and add the month, and then add 0 months to get the period of the current month. For example, if the current date is 2023-12-15, the query will return 202312, which represents the period of December 2023.

Example 5: Get the period of the next month

We can use the PERIOD_ADD() function with the YEAR() and MONTH() functions, and add 1 month to get the period of the next month. For example:

SELECT PERIOD_ADD(YEAR(CURDATE()) * 100 + MONTH(CURDATE()), 1) AS result;

This query will get the year and month of the current date, multiply the year by 100 and add the month, and then add 1 month to get the period of the next month. For example, if the current date is 2023-12-15, the query will return 202401, which represents the period of January 2024.

There are some other functions that are related to the PERIOD_ADD() function, and can be useful for working with periods. Here are some of them:

  • PERIOD_DIFF(): This function returns the number of months between two periods. For example, PERIOD_DIFF(202312, 202301) returns 11.
  • YEAR(): This function returns the year of a date or datetime value. For example, YEAR('2023-12-15') returns 2023.
  • MONTH(): This function returns the month of a date or datetime value. For example, MONTH('2023-12-15') returns 12.
  • CURDATE(): This function returns the current date as a date value. For example, CURDATE() returns ‘2023-12-15’ if the current date is 2023-12-15.

Conclusion

In this article, we learned how to use the MySQL PERIOD_ADD() function, which adds a number of months to a period and returns the resulting period. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with periods.