CODEWITHSUNDEEP
mysql
Ravi Ranjan
Ravi Ranjan

Reputation: 125

how to take the month difference of two dates in MySQL

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');

Upvotes: 3

Views: 13638

Answers (5)

Urvesh Parmar
Urvesh Parmar

Reputation: 64

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())

Upvotes: -1

Gaj
Gaj

Reputation: 886

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'))

Upvotes: 0

Roshana Pitigala
Roshana Pitigala

Reputation: 8806

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

Upvotes: 14

Ezra Johnson
Ezra Johnson

Reputation: 21

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

Upvotes: 0

Riajul Islam
Riajul Islam

Reputation: 1483

Please use this code 

SELECT TIMESTAMPDIFF(MONTH, '2014-10-17T00:00:00.000-07:00','2015-02-06T00:00:00.000-08:00')

Upvotes: 3

Related Questions