How to get difference between two dates in months using MySQL query?

by

Month-difference between any given two dates:

I’m surprised this hasn’t been mentioned yet:

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')
-- Outputs: 0

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

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

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

It basically gets the number of months elapsed from the first date in the parameter list. This solution automatically compensates for the varying amount of days in each month (28,30,31) as well as taking into account leap years — you don’t have to worry about any of that stuff.

Month-difference with precision:

It’s a little more complicated if you want to introduce decimal precision in the number of months elapsed, 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

More Related Contents:

  1. Count days between two dates, excluding weekends (MySQL only)
  2. Get a list of dates between two dates
  3. How to populate a table with a range of dates?
  4. How to convert a string to date in MySQL?
  5. MySQL Query GROUP BY day / month / year
  6. Converting a date in MySQL from string field
  7. Mysql: Select all data between two dates
  8. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  9. MySQL Query – Records between Today and Last 30 Days
  10. What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?
  11. Difference between two dates in MySQL
  12. Select mysql query between date?
  13. How to select date from datetime column?
  14. Get the latest date from grouped MySQL data
  15. mysql: get record count between two date-time
  16. How to get first day of every corresponding month in mysql?
  17. MySQL Select last 7 days
  18. How to select the most recent set of dated records from a mysql table
  19. How to cast DATETIME as a DATE in mysql?
  20. How do I get the first day of the week of a date in mysql?
  21. MySQL date format
  22. Compare only day and month with date field in mysql
  23. How to select only date from a DATETIME field in MySQL?
  24. Select all months within given date span, including the ones with 0 values
  25. Update date + one year in mysql
  26. Insert/ Update random date in MySQL
  27. mysql date comparison with date_format
  28. How to compare timestamp dates with date-only parameter in MySQL?
  29. MySQL cumulative sum grouped by date
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment