2

how to take the month difference of two dates in MySQL.

I m trying to get the month difference of two dates but I'm getting no. of days.

select datediff('2014-10-17T00:00:00.000-07:00', '2015-02-06T00:00:00.000-08:00');
Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
Ravi Ranjan
  • 115
  • 1
  • 1
  • 12
  • 2
    Possible duplicate of [How to calculate difference between two dates in months in MySQL](https://stackoverflow.com/questions/5633821/how-to-calculate-difference-between-two-dates-in-months-in-mysql) – skelwa Jul 03 '18 at 10:57
  • Try this `Select PERIOD_DIFF(MONTH(date1),MONTH(date2)) from table;` – Sodium Jul 03 '18 at 11:07
  • @GauravGenius PERIOD_DIFF should be in format %Y%m not month only – Gaj Jul 03 '18 at 11:17
  • @Gaj, right thanks for reminding me. – Sodium Jul 03 '18 at 11:35
  • @RaviRanjan Please accept the answer which helped you most in solving your problem. It helps future readers. If the answers weren't helpful leave comments below them. So the poster can update them accordingly. – Roshana Pitigala May 14 '19 at 13:29

5 Answers5

13

TIMESTAMPDIFF()

is your solution.

Syntax would be

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2);

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.

~MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions~

Legal values for unit

  • MICROSECOND (microseconds)
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

Examples

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885
Community
  • 1
  • 1
Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
3

Please use this code 

SELECT TIMESTAMPDIFF(MONTH, '2014-10-17T00:00:00.000-07:00','2015-02-06T00:00:00.000-08:00')
Riajul Islam
  • 1,425
  • 15
  • 19
0

This could help,

SELECT 12 * (YEAR(STR_TO_DATE('01/01/2011', '%d/%m/%Y')) - 
YEAR(STR_TO_DATE('01/01/2010', '%d/%m/%Y'))) 
+ (MONTH(STR_TO_DATE('01/01/2011', '%d/%m/%Y')) 
- MONTH(STR_TO_DATE('01/01/2010', '%d/%m/%Y'))) AS months
Ezra Johnson
  • 21
  • 3
0

Try this

Select PERIOD_DIFF(Date_format('2015-02-06T00:00:00.000-08:00', '%Y-%m'), Date_format('2014-10-17T00:00:00.000-07:00', '%Y%m'))
Gaj
  • 888
  • 5
  • 5
-1
in MySQL server 
DATEDIFF is built in function
You can get Difference of 
Day
Month
Year

SELECT DATEDIFF(DAY,'2018-05-01',GetDate())
SELECT DATEDIFF(Month,'2018-05-01',GetDate())
SELECT DATEDIFF(Year,'2018-05-01',GetDate())
Urvesh Parmar
  • 54
  • 2
  • While this may answer the question it's better to add some description on how this answer may help to solve the issue. Please read [How do I write a good answer](https://stackoverflow.com/help/how-to-answer) to know more. – Roshana Pitigala Jul 03 '18 at 16:43
  • 1
    MySql DateDiff does not support this syntax. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff – John C Nov 02 '21 at 02:29