0

Is there any method in mysql database which return the difference between two dates in months and days. for eg if i provide 01-01-2018 as fromdate and 02-02-2018 as todate i want to get the result as 1 month 1 day.

user3292629
  • 138
  • 2
  • 16
  • https://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql – Tum Jan 29 '18 at 10:30
  • i want month as well as day. Not month alone. the link u provided talks only about month. i want to have both in one result. – user3292629 Jan 29 '18 at 10:33
  • then put `DAY` or `DAYOFMONTH` instead of `MONTH`; and if you want both, just put one with `MONTH` and put another one with `DAYOFMONTH` :) – NatNgs Jan 29 '18 at 10:38
  • i want the difference between two dates in month and day. if i give 01-01-2018 as fromDate and 02-02-2018 as toDate i want to get the output as 1 month 1 day. Is there any method like that. – user3292629 Jan 29 '18 at 10:42

1 Answers1

0
SELECT FLOOR(DATEDIFF('2018-02-01', '2018-01-01')/30) AS months, DATEDIFF('2018-02-01', '2018-01-01')%30 AS days;

The only problem is that is calculates months as 30 days. So it doesn't take into account how many days the current month is.

UPDATE:

A solution for taking month length into account

SELECT 
(CASE WHEN (DAY('2018-02-02') >= DAY('2018-01-01')) 
THEN
    (YEAR('2018-02-02')*12 + MONTH('2018-02-02')) - (YEAR('2018-01-01')*12 + MONTH('2018-01-01'))
ELSE
    (YEAR('2018-02-02')*12 + MONTH('2018-02-02')) - (YEAR('2018-01-01')*12 + MONTH('2018-01-01')) - 1
END) AS months,
(CASE WHEN (DAY('2018-02-02') >= DAY('2018-01-01')) 
THEN
    DAY('2018-02-02') - DAY('2018-01-01')
ELSE
    (DAY(LAST_DAY('2018-01-01')) - DAY('2018-01-01')) + DAY('2018-02-02')
END) AS days;

You should insert the dates as a variable (replace '2018-01-01' with fromdate and '2018-02-02' with todate).

Explanation:

It has 2 cases for both months and days:

  • Case 1: the day of the month of todate is higher than (or equal to) the date of the month of fromdate. In this case the number of months is the number of years * 12 + the number of months of todate, minus the same calculation for fromdate. The number of days is simply the day number of todate minus the day number of fromdate.
  • Case 2: the day of the month of todate is lower than the date of the month of fromdate. In this case the number of months is the number of years * 12 + the number of months of todate, minus the same calculation for fromdate, minus 1 (because the month isn't 'full'). The number of days is the last day of the month of fromdate, minus the day of fromdate, plus the day of the month of todate. Or in other words: the amount of days passed in the month of fromdate since the day of that month, plus the days passed in the month of todate.
Tum
  • 6,937
  • 2
  • 25
  • 23