MySQL PERIOD_ADD() function
In this context, we will learn how we can use the MySQL PERIOD_ADD() function with proper syntax and good examples.
Introduction of MySQL PERIOD_ADD() function
In MySQL, PERIOD_ADD() function will help to add a specific number of months to a given period. The PERIOD_ADD() function will return the resultant value in the ‘YYYYMM‘ format.
Syntax of the MySQL PERIOD_ADD() function
The syntax of the MySQL PERIOD_ADD() function is given as follows:
PERIOD_ADD(prd, num)
Parameters or arguments used in MySQL PERIOD_ADD() function:
There are two parameters accepted by the PERIOD_ADD() function in MySQL, which are given as follows:
PRD:
This is the period that should be in YYMM OR YYYYMM format.
Num:
This parameter is the number of months that will be added to a given period; the value can be negative or positive.
Returns:
The function will return the resultant value after adding a specific number of months to the given period.
Application used for PERIOD_ADD() function:
The PERIOD_ADD() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL PERIOD_ADD() function:
Now we will look into some MySQL PERIOD_ADD() function examples and will explore how we can use the PERIOD_ADD function in MySQL.
Example 1 :
In this example, we will add months to a given period using PERIOD_ADD() function in MySQL.
SELECT PERIOD_ADD(202011, 9) As New_period;
Output:
New_period
202108
1 row in set (0.00 sec)
Example 2:
In this example, we will subtract months from a given period using PERIOD_ADD() function in MySQL.
SELECT PERIOD_ADD(202102, -5) As New_period;
Output:
New_period
202009
1 row in set (0.00 sec)
Example 3:
In the given example, we will add and Subtract months from a two-digit year period in MySQL.
SELECT
PERIOD_ADD(2109, -5) As New_period1,
PERIOD_ADD(2109, +5) As New_period2;
Output:
New_period1 | New_period2 |
202104 | 202202 |
1 row in set (0.00 sec)
Example 4:
In this example, we will use the Current Date and Extract functions in MySQL.
SELECT
CURDATE( ) AS 'Curr_date',
EXTRACT(YEAR_MONTH FROM CURDATE( )) AS 'Curr_period',
PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE( )), 11) AS 'New_period';
Output:
Curr_date | Curr_period | New_period |
2020-11-30 | 202011 | 202110 |
Application of MySQL PERIOD_ADD() function:
This function is used to find the Remainder of one number divided by another.
Summary:
In the above context, we have learned how we can use the PERIOD_ADD() function in MySQL used to find the Remainder of one number divided by another.