Date and Time Functions in V5R1 RPG

August 9, 2002 Timothy Prickett Morgan

Hey, Ted:

Here’s a snippet of RPG code that loads timestamp variables from legacy date formats and date/time variables.

It shows how to use three new built-in functions to make date/time comparisons easier than they used to be.

The problem arises when dates and times are stored in separate fields and must be compared.

Maybe the dates and times are in fields of the date and time data types, but in many databases, they’re stored in plain old alpha or numeric fields.

To compare two date/time combinations requires logic like the following example:

* is job-on date/time before scheduled date/time?
C                   if        (job_on_date < sched_date) or 
C                             (job_on_date = sched_date and 
C                              job_on_time < sched_time)    

This is cumbersome, and I have seen many places where the comparison was done incorrectly.

I convert all date and time pairs to timestamps before comparison. The %date, %time, and %timestamp built-in functions, introduced in V5R1, simplify such conversions.

Use this code to convert a legacy date and time to a timestamp:

H DATFMT(*ISO)   TIMFMT(*ISO)
D legacy_time     s              6p 0 inz(073403)
 * HH MM SS
D legacy_date     s              6p 0 inz(071802)
 * MM DD YY
D legacy_stamp    s               z   inz
/free
 legacy_stamp = %timestamp(
     %char(%date(legacy_date:*MDY)) + '-' +
     %char(%time(legacy_time:*hms)) + '.000000');
/end-free

Use this code to convert date and time data types to a timestamp:

H DATFMT(*ISO)   TIMFMT(*ISO)
D defined_time    s               t   inz
D defined_date    s               d   inz
D defined_stamp   s               z   inz
 /free
  defined_stamp = %timestamp(
       %char(defined_date) + '-' +
       %char(defined_time) + '.000000');
 /end-free

— Craig Rutledge, www.jcrcmds.com

Sponsored By COMMON

REGISTER FOR COMMON IN DENVER, OCT. 13-17 Get the IT training you need by attending COMMON Users Group’s Fall 2002 IT Education Conference & Expo, October 13-17 in Denver. Early Bird registration is $1,150 until September 4. Choose from over 720 sessions and labs covering a wide range of industry topics. Also receive training from J.D. Edwards, MAPICS, and other vendors. Don’t miss out! Go to www.common.org