Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
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.