MONTHS_BETWEEN Function v6.1

New in SQL for IBM i on v6r1 is the MONTHS_BETWEEN function. This function calculates the number of months between two dates. It says it "estimates" the duration because it calculates fractional months, however in most cases whole numbers are good enough.

It returns what looks like a floating point value, but I think its really just a huge decimal value. I tend to wrap it in Integer( months_between( date1, date2) ) when I use it. Here's the syntax:

MONTHS_BETWEEN ( expression1 , expression2 )

Specify the two date values as parameters 1 and 2 (timestamp or date data-types are supported) and use the returned it in your SQL SELECT, UPDATE or other statement, such as the SET statement.

There is also a TIMESTAMP_DIFF function that is more flexible. I've not looked at it, but the IBM documentation for it shows the following example:

SELECT
  TIMESTAMP_DIFF(64, CAST(CURRENT_TIMESTAMP-CAST(BIRTHDATE AS TIMESTAMP)
                AS CHAR(22)))
    AS AGE_IN_MONTHS
  FROM EMPLOYEE;

The first parameter is the duration code, where 64=Months.

While the SQL MONTHDIFF() function I wrote in RPG IV works perfectly on any release, if you're already on v6r1 or later (as most people are) then consider using MONTHS_BETWEEN to get a duration in MONTHS.