[mysql] The difference in months between dates in MySQL

From the MySQL manual:

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

So it may be possible to do something like this:

Select period_diff(concat(year(d1),if(month(d1)<10,'0',''),month(d1)), concat(year(d2),if(month(d2)<10,'0',''),month(d2))) as months from your_table;

Where d1 and d2 are the date expressions.

I had to use the if() statements to make sure that the months was a two digit number like 02 rather than 2.

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to datediff

DATEDIFF function in Oracle How to calculate DATE Difference in PostgreSQL? How to convert number of minutes to hh:mm format in TSQL? Difference between two dates in years, months, days in JavaScript Number of days between past date and current date in Google spreadsheet How to compare two dates to find time difference in SQL Server 2005, date manipulation SQL Server Group By Month How do I check the difference, in seconds, between two dates? Date difference in years using C# Difference in days between two dates in Java?

Examples related to date-arithmetic

How to subtract hours from a date in Oracle so it affects the day also MySQL Select last 7 days Calculating days between two dates with Java How to find the duration of difference between two dates in java? Check if year is leap year in javascript Add a month to a Date Get the difference between two dates both In Months and days in sql Oracle Date - How to add years to date How can I do time/hours arithmetic in Google Spreadsheet? Oracle DateTime in Where Clause?