Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
MONTHS_BETWEEN SQL Function in IBM i v6.1
has no ratings.
Published: 21 Oct 2011
Revised: 23 Jan 2013 - 1553 days ago
Last viewed on: 23 Apr 2017 (3308 views) 

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.

MONTHS_BETWEEN SQL Function in IBM i v6.1 Published by: Bob Cozzi on 21 Oct 2011 view comments

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.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS