OPNQRYF Date Arithmetic

September 25, 2002 Timothy Prickett Morgan

Hey, Ted:

I need to select records for the period beginning 210 days ago and ending 30 days ago using Open Query File (OPNQRYF). The date field is eight digits packed, in YYYYMMDD format.

I thought this was going to be easy, but I am a little tired and stumped.

So how do I do this?

— David

You’ll have to convert that numeric date to a date data type in order to use OPNQRYF’s date duration functions.

Here’s an example that illustrates the conversion:

DCL        VAR(&QRYSLT) TYPE(*CHAR) LEN(512)
                                                         
DCL        VAR(&QRYSLT) TYPE(*CHAR) LEN(512)

CHGVAR     VAR(&QRYSLT) + 
             VALUE('DtDate *ge (%curdate - %durday(210)) *and +
                    DtDate *le (%curdate - %durday(30))')
OPNQRYF    FILE((GLJNLY)) QRYSLT(&QRYSLT) +
             MAPFLD((DIGDATE '%DIGITS(jedate)' *CHAR 8) +
                    (EDDATE '%sst(digdate 5 2) *cat "/" +
                        *cat %sst(digdate 7 2) *cat "/" +
                        *cat %sst(digdate 3 2)' *CHAR 8) +
                    (DTDATE '%date(eddate)' *DATE))    
CPYFRMQRYF FROMOPNID(GLJNLY) TOFILE(QTEMP/X) +
             MBROPT(*REPLACE) CRTFILE(*YES)
CLOF       OPNID(GLJNLY)
RUNQRY     QRYFILE((QTEMP/X))

The trick to this is in the three mapped fields. DIGDATE uses the %digits function to convert the numeric field to an eight-byte character field. EDDATE uses substringing and concatenation to reformat DIGDATE into the system format, which, on the Netshare/400 machine on which I created this example, is MM/DD/YY. The DTDATE field changes EDDATE to the date data type. The QRYSLT parameter selects records based on DTDATE, not the original field JEDATE.

— Ted

Sponsored By COMMON

COMMON IT EXECUTIVE CONFERENCE Tell your IT Directors about the three-day event that is specially designed for them. Held October 13-15, 2002, in Denver, the IT Executive Conference will give iSeries Directors the opportunity to network and learn from well-known speakers in the iSeries environment, and receive new strategies for optimizing operations. To find out more, go to: http://www.common.org/executive