MONTHS_BETWEEN('2018-12-31', '2019-02-28') outputs 1?

Question

Simply shown above, this has been causing inconsistencies with our data. I'm currently writing an AMDP Logic that makes use of the difference between the months of each records, but surprisingly, the function MONTHS_BETWEEN() is not consistent for all cases and were seemingly skipping months for very specific cases. I don't know what is causing this. Is there a way to fix this inconsistency or some sort of alternate solution if none? Thanks.

Answer 1

Thanks to the comments, I figured out that the result from the function MONTHS_BETWEEN() is being calculated according to the number of days between. I initially assumed that it only uses the Month's value for the calculation, hence the expectation that Feb 2019 was 2 months away from Dec 2018.

Given that, I was able to come up with quite a fix that converts the dates into the first day of the month to get a guaranteed difference in months for any two given date values.

Here's a snippet of the SQL Code and the corresponding result:

This workaround is followed by an assumption that the dates being compared are all the last days of any given month. If that's not the case then a pre-processing by using the LAST_DAY() function will be needed to be inserted inside the NEXT_DAY() function in order for it to work similarly.

***EDIT: Manually editing the string date value using LEFT() and CONCATENATING with '01' seems to be a more efficient and straightforward approach. I can't upload the screenshot but here's the working code:

SELECT MONTHS_BETWEEN(
         LEFT('2018-12-31', 8) || '01',
         LEFT('2019-02-28', 8) || '01'
       ) AS dmonths
  FROM dummy