3

enter image description here

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.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Piolo Quintos
  • 75
  • 6
  • it seems correct. count the days then determine the floor for months – Golden Lion Oct 12 '22 at 14:17
  • It can be remarkably hard to define how many months there are between two dates. For example, which date is 3 months before May 31? Should it be March 3 or February 28? What about on a leap year? – EJoshuaS - Stand with Ukraine Oct 12 '22 at 14:26
  • Oh, so is the function MONTHS_BETWEEN() calculating the months based off the number of days? I initially thought it only uses the months value like Feb 2019 is 2 months away from Dec 2018, so I was expecting an output of 2 instead of 1. – Piolo Quintos Oct 12 '22 at 14:30
  • The problem is, you know that you're interested in the last day of those respective months. However, that second date might just be the date 27 days after the first of the month. The fact that it's in February and is in fact the last day of the month... There's no way to encode the *intended* meaning in simple datetime datatypes like these, because the same raw date value can be reached by an infinite number of different pieces of logic. – Damien_The_Unbeliever Oct 12 '22 at 14:40
  • 1
    It's a shame really that they recognise this sort of problem with `ADD_MONTHS` and `ADD_MONTHS_LAST` but don't offer a corresponding `MONTHS_BETWEEN` variant. – Damien_The_Unbeliever Oct 12 '22 at 14:46
  • It is really DBMS-dependent. Oracle, SQL Server and DB2 return 2 (treating this days as end of month) while Postgres and MySQL return 1 (number of full 31-day months). See [db<>fiddle](https://dbfiddle.uk/cK1j9Tba): all SQL statements in one fiddle, just switch engines. – astentx Oct 14 '22 at 07:47

1 Answers1

2

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: enter image description here

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
Piolo Quintos
  • 75
  • 6