MySQL - PERIOD_DIFF() Function

Quiz

MySQL PERIOD_DIFF() function is similar to the PERIOD_ADD() function. It is used to find the difference between two period values. In MySQL, a period is defined as a combination of year and month; and it will be represented in the format, YYMM or YYYYMM.

This function accepts two period values as parameter values, calculates the difference between them and returns the results (in the form of number of months).

A NULL value is returned if either of the arguments passed to the function are NULL.

Syntax

Following is the syntax of MySQL PERIOD_DIFF() function −

PERIOD_DIFF(P1,P2)

Parameters

This method accepts two parameters. The same is described below −

P1 is the value period value. The format is "YYMM" or "YYYYMM"
P2 is another period value that we want to subtract.

Return value

This function returns the difference in months between periods P1 and P2.

Example

In the following example, we are using the PERIOD_DIFF() function to calculate the difference between two given period values −

SELECT PERIOD_DIFF(202409, 202309) As Result;

Output

This will produce the following result −

Result
12

Example

Following is another example of this function −

SELECT PERIOD_DIFF('2405', '2305') As Result;

Output

Following is the output −

Result
12

Example

In this example, we have created a table named PLAYERS using the following CREATE TABLE query

CREATE TABLE PLAYERS(
   ID int,
   NAME varchar(255),
   DATE_OF_BIRTH date,
   DOB_PERIOD varchar(30),
   Country varchar(255),
   PRIMARY KEY (ID)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar Dhawan', DATE('1981-12-05'), 198112, 'India'),
(2, 'Jonathan Trott', DATE('1981-04-22'), 198104, 'SouthAfrica'),
(3, 'Kumara Sangakkara', DATE('1977-10-27'), 197710, 'Srilanka'),
(4, 'Virat Kohli', DATE('1988-11-05'), 198811, 'India'),
(5, 'Rohit Sharma', DATE('1987-04-30'), 198704, 'India'),
(6, 'Ravindra Jadeja', DATE('1988-12-06'), 198812, 'India'),
(7, 'James Anderson', DATE('1982-06-30'), 198206, 'England');

Execute the below query to fetch all the inserted records in the above-created table −

Select * From PLAYERS;

Following is the PLAYERS table −

ID	NAME	DATE_OF_BIRTH	DOB_PERIOD	COUNTRY
1	Shikhar Dhawan	1981-12-05	198112	India
2	Jonathan Trott	1981-04-22	198104	SouthAfrica
3	Kumara Sangakkara	1977-10-27	197710	Srilanka
4	Virat Kohli	1988-11-05	198811	India
5	Rohit Sharma	1987-04-30	198704	India
6	Ravindra Jadeja	1988-12-06	198812	India
7	James Anderson	1982-06-30	198206	England

Here, we are using the MySQL PERIOD_DIFF() function to calculate the difference in periods between "DOB_PERIOD" and the fixed period '197012' −

SELECT ID, NAME, DATE_OF_BIRTH, DOB_PERIOD, PERIOD_DIFF(DOB_PERIOD, 197012)
As Result From PLAYERS;

Output

The output is displayed as follows −

ID	NAME	DATE_OF_BIRTH	DOB_PERIOD	Result
1	Shikhar Dhawan	1981-12-05	198112	132
2	Jonathan Trott	1981-04-22	198104	124
3	Kumara Sangakkara	1977-10-27	197710	82
4	Virat Kohli	1988-11-05	198811	215
5	Rohit Sharma	1987-04-30	198704	196
6	Ravindra Jadeja	1988-12-06	198812	216
7	James Anderson	1982-06-30	198206	138

Print Page