Re: AS/400 query with date - 6 months

On 30-Jun-2016 10:29 -0500, Steinmetz, Paul wrote:

Below is my query select.

ODUDATCYMD LT '1160130'

DSPOBJD to an output file results with ODUDAT Last used date
(MMDDYY)

To compare an object's last use date, the ODUDAT needs to
be reformatted with the century added.

ODUDATCYMD is a result field from century year month day.

Result Fld Expression
: ColHdg

ODUDATY substr(ODUDAT, 5, 2)
ODUDATM substr(ODUDAT, 1, 2)
ODUDATD substr(ODUDAT, 3, 2)

ODUDATCYMD ODUCEN||ODUDATY||ODUDATM||ODUDATD
: Last Used Date (CYYMMDD)

Are there really any objects remaining on the system with usage dates from the 1900s? :-)

I would like to replace the hardcoded literal '1160130' with a
variable.

If the Query/400 QRYDFN is going to be used, then I would suggest creating a VIEW that maps all of the /date/ values into actual dates [null for blanks]. Redefine the Query Definition to refer to that VIEW. Having decided on that approach, the VIEW also could be used to encapsulate the selection using a GLOBAL VARIABLE. That would rid the query of the selection, thus the use of a literal or variable in the QRYDFN is rendered moot. A CREATE OR REPLACE VARIABLE can be run in the program, prior to the RUNQRY being issued against the VIEW, to effect an assignment of the value to the named variable.