PERIOD_ADD() Function Examples – MySQL

PERIOD_ADD() Function Examples – MySQL

In this MySQL tutorial – we would love to share with you how to works MySQL period_add() function with examples.

Here, you will learn how to add specific months in the given period, also learn how to use this query with database tables.

MySQL PERIOD_ADD() Function

In MySQL, PERIOD_ADD () function that allows you to add several months to a certain period. It returns a value in YYYYMM format.

Syntax

The syntax of the MySQL PERIOD_ADD() function is below:

PERIOD_ADD(Period,Num)

Here: Period is the period, and Num is the number of months to add.

Example-1

Let’s take a simple example of this function. The example is below:

SELECT PERIOD_ADD(201901,2);

Output-1

+----------------------+
| PERIOD_ADD(201901,3) |
+----------------------+
|               201904 |
+----------------------+

We added three months to the given period.

Example-2

Lets’s take an example that adds a negative number of months in given period.

SELECT PERIOD_ADD(202101,-2);

Output-2

+-----------------------+
| PERIOD_ADD(201905,-1) |
+-----------------------+
|                201904 |
+-----------------------+

Example 3 – Two Digit Years

This example uses the component of the only two-digit year. Here first two number is a year and second to number is a month, after common that the number of periods adds to the given period.

SELECT PERIOD_ADD(1901,2);

Output-3

+--------------------+
| PERIOD_ADD(1901,2) |
+--------------------+
|             201903 |
+--------------------+

Here, you will notice that the given result by this query is a year in four digits.

Example-4

This example derives the period from the current date. It then adds a month on to that period.

    SELECT 
    CURDATE( ) AS 'Current Date',
    EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Current Period',
    PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE( )), 1) AS 'Next Period';

Output-4

+--------------+----------------+-------------+
| Current Date | Current Period | Next Period |
+--------------+----------------+-------------+
| 2019-07-21   |         201907 |      201908 |
+--------------+----------------+-------------+

Example-5

Now we take another example with MySQL database.

  SELECT
  payment_date AS 'Payment Date',
  EXTRACT(YEAR_MONTH FROM payment_date) AS 'Payment Period',
  PERIOD_ADD(EXTRACT(YEAR_MONTH FROM payment_date), 6) AS 'Next Payment'
  FROM payments
  WHERE id = 1;

Output-5

+---------------------+----------------+--------------+
| Payment Date        | Payment Period | Next Payment |
+---------------------+----------------+--------------+
| 2018-01-10 12:50:37 |         201801 |       201806 |
+---------------------+----------------+--------------+

Conclusion

Here, you have learned how to use MySQL PERIOD_ADD() function with various examples.

You may like

  1. TIMESTAMP() Function Examples – MySQL
  2. TIMEDIFF() Function Examples – MySQL
  3. SEC_TO_TIME() Function Examples – MySQL
  4. QUARTER() Function Examples – MySQL
  5. STR_TO_DATE() Function Examples – MySQL
  6. PERIOD_DIFF() Function Examples – MySQL
  7. SECOND() Function Examples – MySQL
  8. MICROSECOND() Function Examples – MySQL
  9. MAKETIME() Function Examples – MySQL
  10. Localtime() Function Examples – MySQL
  11. CURTIME() Function Examples – MySQL
  12. MySQL CURRENT TIMESTAMP Function Examples
  13. MySQL CURRENT TIME Function Examples
  14. ADDTIME() Function Examples – MySQL
  15. MINUTE() Function Examples – MySQL
  16. HOUR() Function Examples – MySQL
  17. NOW() MySQL Function | MySQL
  18. MySQL DATE() And TIME() Functions (List)
  19. TIMESTAMPADD() Function Examples – MySQL
  20. TIMESTAMPDIFF() Function Examples – MySQL

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *