Hi,
I ran the below experiment with DATETIME_DIFF.
It seems the units argument only performs a simple division, but most implementations I have seen of this function always compare the actual units.
Am I missing something?
@Elias_Gomez_Sainz is right - just using DATETIME_DIFF() on two, unprocessed date fields is going to calculate the difference in the Unix time stamp, which is a just a big number that represents elapsed seconds since the beginning of the Unix time stamp “epoch” (January 1st, 1970, UTC).
So, contrary to your expectations, it is not comparing the number of the month from the first date to the number of the month in the second date, and performing a diff calc on those. Rather, it is getting the total difference in seconds between the two dates (as a whole), converting that to months through division, then rounding to the nearest integer, which in the case of a difference less than 30 days, will evaluate to “0 months”.
What @Elias_Gomez_Sainz is suggesting is doing a simple subtraction operation on the isolated month units, like this:
MONTH(Date1) - MONTH(Date2)
That will get you the difference between the month integers from the dates in question, BUT this will not be able to account for a difference across years, as you are removing years from the equation. So, for example,
MONTH(3/1/2018) - MONTH(12/1/2017) = -9
where if considering years, you might want it to return 4.
Does that help?
