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

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.

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

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.

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

I hope this article will help you to understand how to calculate the number of months between two specific dates in MySQL. 

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts