CODEWITHSUNDEEP
sqloracle-databasedatetimestamp
Salvatore Vitale
Salvatore Vitale

Reputation: 17

Oracle Months Between does not work properly

Good morning, I've wrote the following query to get the months difference between a date column (STRT_DT) and a timestamp column (VLD_FRM_TMS) stored in two different tables. I don't know why it works for some records but does not for others (it calculates one month less)

select ID, floor (months_between (cast(a.VLD_FRM_TMS as date), STRT_DT)) as delta
from TABLE_A a
inner join TABLE_B b
on a.ID = b.ID

This is an example of record for which the calculation does not work:

VLD_FRM_TMS
-----------
28-FEB-21 12.00.00.000000000 AM

STRT_DT
--------
29-OCT-20

The formula calculates 3 months instead of 4...

Could anyone help me in locating the problem? Thank you in advance

Upvotes: 0

Views: 771

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

I actually want the months difference regardless of the specific day of the month.

You can truncate both values to the first of the month using the 'MM' format element, and then get the difference between those:

months_between (trunc(a.VLD_FRM_TMS, 'MM'), trunc(STRT_DT, 'MM')) as delta

That will now always be an integer - i.e. a whole number of months - so you don't need to trunc/floor/round the result.

db<>fiddle showing the problem with the old calculation and this version.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is exactly the behavior that is described in the documentation:

If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

(Note: Highlighting is mine.)

If you run this code:

select months_between(date '2021-02-28', date '2020-10-29') as delta
from dual

The result is 3.9677.

I suspect that you want some other logic. However, the question does not specify what logic you actually do want.

Upvotes: 3

Related Questions