Q. I have an interactive program that allows the user to search through the database. I would like to give the user the capability to search on a date field. The field is stored in the database as YYMMDD, but I need to provide search capabilities in the format DDmonth abbreviationYY (e.g., 04JUN96). For example, the user may search on *JU* and I need to return records for the months of June and July.

I'm using Open Query File (OPNQRYF) to query the database inside an RPG III program. I would like to avoid creating another file with the appropriately formatted date because this is an interactive program.

- Aau Urtubia

A. You have to convert the date field in the database to DDmonth abbreviationYY format to compare it to the wildcard search value. In 7, you can see how I accomplished this feat.

A. You have to convert the date field in the database to DDmonth abbreviationYY format to compare it to the wildcard search value. In Figure 7, you can see how I accomplished this feat.

The passed-in value (&SEARCH) is what you're searching for, such as *JU*. The power is in the MAPFLD parameter.

Here's what each mapped field does.

o MONTHLIST: a list of three character abbreviations. Notice that the value in this variable begins with three blank spaces.

o CHARDATE: the database date field (SDTRDJ) converted to character. If the database date is in character format, skip this step and use the database field

name instead of CHARDATE in the mapped field definitions that follow.

o MONTH: the month portion of the data-base date as a zoned decimal number.

o OFFSET: the position in the MONTH

LIST where the abbreviation for the month starts. January starts at position 4, February at position 7, and so on.

o SLTDATE: the database date reformatted as DDmonth abbreviationYY. It gets compared to the search string in the QRYSLT parameter.

- Ted Holt

TechTalk: Programming--This solution allows the user to search using a text month name in a numeric date.

Figure 7: Sample Source to Search Database Based on Month Name

 PGM PARM(&SEARCH) DCL VAR(&SEARCH) TYPE(*CHAR) LEN(7) DCL VAR(&QRYSLT) TYPE(*CHAR) LEN(256) CHGVAR VAR(&QRYSLT) VALUE('SLTDATE *EQ %WLDCRD("' + *CAT &SEARCH *TCAT '")') OVRDBF FILE(TESTPF2) SHARE(*YES) OPNQRYF FILE((TESTPF2)) QRYSLT(&QRYSLT) + MAPFLD((MONTHLIST '" + JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC"') + (CHARDATE '%DIGITS(SDTRDJ)' *CHAR 6) + (MONTH '%SST(CHARDATE 3 2)' *ZONED 2 0) + (OFFSET 'MONTH * 3 + 1') (SLTDATE + '%SST(CHARDATE 5 2) *CAT %SST(MONTHLIST + OFFSET 3) *CAT %SST(CHARDATE 1 2)')) CALL PGM(LISTPGM) CLOF OPNID(TESTPF2) ENDPGM