;

# How to Calculate the Number of Months between two specific dates in MySQL

###### Tutorialsrack 01/03/2020 SQL MySQL

In this article, you will learn how to calculate the number of months between two specific dates in MySQL.

To get a number of months between two specific dates, we use the `TIMESTAMPDIFF` function and `PERIOD_DIFF` function of MySQL.

## TIMESTAMPDIFF Function

This MySQL `TIMESTAMPDIFF` function is used to calculate the difference between two DATE or DATETIME values.

#### Syntax of TIMESTAMPDIFF Function

##### Syntax
``TIMESTAMPDIFF(unit,startDate,endDate);``

This function takes 3 parameters as follows:

• Parameter 1 - unit, it is a type of output you want, such as MICROSECOND, SECOND MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
• Parameter 2 - Begin Date or Datetime Values
• Parameter 3 - End Date or Datetime Values

Example: In this example, we will calculate a number of months between two specific dates using `TIMESTAMPDIFF` function in MySQL.

##### Example - TIMESTAMPDIFF function
``````SELECT TIMESTAMPDIFF(MONTH, '2020-01-05', '2020-02-20') as 'Total No. of Month Between Two Specific Date';
-- Output will be 1

SELECT TIMESTAMPDIFF(MONTH, '2018-01-05', '2020-02-20') as 'Total No. of Month Between Two Specific Date';
-- Output will be 25

SELECT TIMESTAMPDIFF(MONTH, '2020-01-05 22:22:00', '2020-12-20') as 'Total No. of Month Between Two Specific Date';
-- Output will be 11

SELECT TIMESTAMPDIFF(MONTH, '2020-01-05 22:22:00', '2020-05-20 23:59:59') as 'Total No. of Month Between Two Specific Date';
-- Output will be 4

-- it will return negative output, if first date is bigger than second one
SELECT TIMESTAMPDIFF(MONTH, '2020-11-05 22:22:00', '2020-05-20 23:59:59') as 'Total No. of Month Between Two Specific Date';
-- Output will be -5``````

## PERIOD_DIFF Function

This MySQL `PERIOD_DIFF()` function is used to calculate the difference between the two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not dated values.

#### Syntax of PERIOD_DIFF Function

##### Syntax
``PERIOD_DIFF(P1,P2);``

Where P1 indicates Period 1 and P2 indicates Period 2.

Example: In this example, we will calculate a number of months between two specific dates using `PERIOD_DIFF()` function in MySQL.

##### Example - PERIOD_DIFF() function
``````SELECT PERIOD_DIFF(date_format('2020-05-20', '%Y%m'), date_format('2020-01-15', '%Y%m')) as 'Total No. of Month Between Two Specific Date';
-- Output will be 4

SELECT PERIOD_DIFF(date_format('2020-05-20 23:59:59', '%Y%m'), date_format('2020-01-15', '%Y%m')) as 'Total No. of Month Between Two Specific Date';
-- Output will be 4

SELECT PERIOD_DIFF(date_format('2020-05-20 23:59:59', '%Y%m'), date_format('2020-01-15 22:22:22', '%Y%m')) as 'Total No. of Month Between Two Specific Date';
-- Output will be 4

-- it returns a negative output, if first date values is smaller than second date values
SELECT PERIOD_DIFF(date_format('2019-05-20 23:59:59', '%Y%m'), date_format('2020-01-15 22:22:22', '%Y%m')) as 'Total No. of Month Between Two Specific Date';
-- Output will be -8``````