;

# 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