11

I have two columns in a MySQL table:

  • DateOfService (datetime)
  • BirthDate (date)

I want to run a MySQL query that will provide date difference between these two fields in months.

How can I do this in a MySQL select query?

Thanks.

Treffynnon
  • 21,365
  • 6
  • 65
  • 98
Awan
  • 18,096
  • 36
  • 89
  • 131
  • Can you give an example of what output you expect? E.g.: today is April 12th and my birthday is April 14th or May 12th or May 15th. What values would you expect to receive from the query? – Teetrinker Apr 12 '11 at 10:35
  • Duplicate: http://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql – manji Apr 12 '11 at 10:37
  • DateOfService column date will always be greater then BirthDate column. So I need to subtract BirthDate from DateOfService and calculate the difference in months. – Awan Apr 12 '11 at 10:38

7 Answers7

50

Have a look at the TIMESTAMPDIFF() function in MySQL.

What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on.

You can specify MONTH as the unit in the first parameter:

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')
-- 0

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')
-- 1

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')
-- 1

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')
-- 7

It basically gets the number of months elapsed from the first date in the parameter list. This solution accounts for the varying amount of days in each month (28,30,31) as well as leap years.

If you want decimal precision in the number of months elapsed, it's a little more complicated, but here is how you can do it:

SELECT 
  TIMESTAMPDIFF(MONTH, startdate, enddate) +
  DATEDIFF(
    enddate,
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate)
    MONTH
  ) /
  DATEDIFF(
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate) + 1
    MONTH,
    startdate + INTERVAL
      TIMESTAMPDIFF(MONTH, startdate, enddate)
    MONTH
  )

Where startdate and enddate are your date parameters, whether it be from two date columns in a table or as input parameters from a script:

Examples:

With startdate = '2012-05-05' AND enddate = '2012-05-27':
-- Outputs: 0.7097

With startdate = '2012-05-05' AND enddate = '2012-06-13':
-- Outputs: 1.2667

With startdate = '2012-02-27' AND enddate = '2012-06-02':
-- Outputs: 3.1935
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • Hey i am able to achieve above result but my condition is that i need 0.5 year , 1 year, 1.5 year, 2 year ,2.5 year and so on.. how could i do that!! – Dipen Mar 03 '15 at 07:44
10

This could work:

SELECT 12 * (YEAR(DateOfService) 
              - YEAR(BirthDate)) 
       + (MONTH(DateOfService) 
           - MONTH(BirthDate)) AS months 
FROM table
Treffynnon
  • 21,365
  • 6
  • 65
  • 98
Marco
  • 56,740
  • 14
  • 129
  • 152
  • i have used this query..issue arises particular date. the dates are 2014-01-30 to 2014-03-02 differs 30days only.but it is returns 2 months difference. – Vaishu Jan 30 '14 at 08:54
4

Try this:

SELECT DATEDIFF(DateOfService, BirthDate) / 30 as months FROM ...

Nikolay Yordanov
  • 1,404
  • 16
  • 25
2

TRY with

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

mysql> SELECT PERIOD_DIFF(200802,200703); -> 11

nsgulliver
  • 12,655
  • 23
  • 43
  • 64
M K
  • 76
  • 4
  • this does not answer the OP question as it does not accept datetime format or even date format. Maybe if this question was edited showing this then I might retract my -1. – amaster Aug 07 '13 at 15:40
2
TIMESTAMPDIFF(MONTH, Start_date, End_date)

Example: 

SELECT TIMESTAMPDIFF(MONTH, BirthDate, DateOfService) AS Months FROM Table
Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
Tuấn Nguyên Minh Thịnh
  • 31
  • 1
2

Based on a summary of all answers and a Google search, I think there are four almost similar ways to write it:

1)

TIMESTAMPDIFF(MONTH, Start_date, End_date) AS Period

E.g. 

TIMESTAMPDIFF(MONTH, MIN(r.rental_date), MAX(r.rental_date)) AS Period

2)

PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months

Or 

PERIOD_DIFF(date_format(End_date(), '%Y%m'), date_format(Start_date, '%Y%m')) as months

E.g. 

PERIOD_DIFF(date_format(MAX(r.rental_date), '%Y%m'), date_format(MIN(r.rental_date), '%Y%m')) as months

3) 

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months

OR 

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM End_date()), EXTRACT(YEAR_MONTH FROM Start_date)) AS months

E.g. 

PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM MAX(r.rental_date)), EXTRACT(YEAR_MONTH FROM MIN(r.rental_date))) as Months

4) 

PERIOD_DIFF(concat(year(d1),if(month(d1)<10,'0',''),month(d1)), concat(year(d2),if(month(d2)<10,'0',''),month(d2))) as Months**

E.g. 

PERIOD_DIFF(
            concat(year(MAX(r.rental_date)),if(month(MAX(r.rental_date))<10,'0',''),month(MAX(r.rental_date))),
            concat(year(MIN(r.rental_date)),if(month(MIN(r.rental_date))<10,'0',''),month(MIN(r.rental_date)))
           ) as Months
Hanif
  • 31
  • 2
2

The 'right' answer depends on exactly what you need. I like to round to the closest whole number.

Consider these examples: 1st January -> 31st January: It's 0 whole months, and almost 1 month long. 1st January -> 1st February? It's 1 whole month, and exactly 1 month long.

To get the number of whole months, use:

SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-01-31');  => 0
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-02-01');  => 1

To get a rounded duration in months, you could use:

SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1
SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1

This is accurate to +/- 5 days and for ranges over 1000 years. Zane's answer is obviously more accurate, but it's too verbose for my liking.

IanS
  • 1,459
  • 1
  • 18
  • 23