[Solved] MYSQL How to perform custom month difference between two dates in MYSQL?

Question

I think this query will do what you want. It uses

  (YEAR(CURDATE())*12+MONTH(CURDATE()))
- (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) -
- 1

to get the number of whole months of experience for the user,

  DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y')))
- DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))
+ 1

to get the number of days in the first month, and

DAY(CURDATE())

to get the number of days in the current month. The two day counts are summed and if the total is > 15, 1 is added to the number of whole months e.g.

SELECT id
     , name
     , (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) - 1 -- whole months
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END -- broken month
       AS months
FROM user

We can use this expression as a JOIN condition between user and allowed_exp_range to find all users who have experience within a given range:

SELECT u.id
     , u.name
     , a.starting_exp_months
     , a.end_exp_months
FROM user u
JOIN allowed_exp_range a
ON (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(u.join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - 1
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END
       BETWEEN a.starting_exp_months AND a.end_exp_months

Output (for your sample data, includes all users as they all fit into one of the experience ranges):

id  name    starting_exp_months     end_exp_months
1   Sam     9                       24
2   Moe     9                       24
3   Tim     0                       6
4   Sal     9                       24
5   Joe     0                       6

I’ve created a small demo on dbfiddle which demonstrates the steps in arriving at the result.

Accepted Answer