Using CURRENT(DATE) to Select Records Using Query/400

Resolving The Problem

Selecting by Date Using Query/400

Query/400 can be used to extract data from a file based on today's date, without having to change the query every day.

Overview

This can be accomplished using the CURRENT(DATE) function provided by Query/400. On the surface, this is a simple process if the fields being compared are already in DATE format. However, many databases were designed to put date information in numeric fields. Query only works with data in numeric fields as numbers. Therefore, the numbers in the numeric field need to be converted to DATE format before they can be used for comparison with today's date.

Other illustrations can be found in the on-line manual, Query for iSeries Use, which is located at the following Knowledge Center Web site:

Query for iSeries Use

Following are the general steps used to do a comparison to the System Date when the "date" field is numeric:

1	Determine DATE format for the job that will run the query.
2	Get today's date from the system.
3	Convert numeric data to DATE format to match the DATE format of the job.
4	Set up selection criteria.

Note: Read Chapter 5 of the Query/400 Use book for background on this process.


Specific Instructions for Each Step

This example is where the "date" field, ORDDATE, is defined as numeric 6 positions with 0 decimal field in MMDDYY format. The meaning of the digits reading left to right in field ORDDATE are month (2 digits), day (2 digits) and year (2 digits).

For more examples, refer to the following Rochester Support Center knowledgebase documents:

For an example for YYMMDD conversion to date data type:
N1010673, Query/400 Converting a YMD Field to a Date Data Type:

For an example for MMDDYY conversion to date data type:
N1010680, Query/400 Conversion of a MDY Field to a Date Data Type:

This is the Define Result Fields portion of the query. See the steps below for the details for each entry.

Field Expression Column Heading Len Dec
SYSDAT CURRENT(DATE)
CORDDATE DIGITS(orddate)
SYSORDDAT SUBSTR(corddate,3,2) || '/' ||
SUBSTR(corddate,5,2) || '/' ||
SUBSTR(corddate,1,2)
DATEFORM date(SYSORDDAT)

Step 1: Determine the DATE Format

One way is to issue CHGJOB, then prompt, then press F10 for more parameters and page down one time. You should see the following:

Job date . . . . . . . . . . . .  061594      Date, *SAME
Date format  . . . . . . . . . .  *MDY        *SAME, *SYSVAL, *YMD, *MDY . . .
Date separator . . . . . . . . .  '/'         *SAME, *SYSVAL, *BLANK, / . . .

This means that the DATE format is MM/DD/YY.

Step 2: Get Today's Date From the System

You can make today's date available to the query by creating the following result field:

Field             Expression             Column Heading    Len   Dec
SYSDAT            CURRENT(DATE)

Note: SYSDAT is a field name I made up to represent today's date. SYSDAT has a length of 8 and format type of L (meaning date). On June 15, 1994, the contents of SYSDAT is '06/15/94'. CURRENT and DATE are functions provided by Query/400.

Step 3: Convert Numeric Data to DATE Format

The conversion is accomplished in three steps.

Step A converts numeric data to character data using the DIGITS function.
Step B arranges the data to match the format of the system date using the SUBSTR (stands for substring) function and concatenation operators (││).
Step C creates a date format field from step B.

Before illustrating steps A through C, I will describe the sample database used in the illustration.

OPEN ORDER FILE
Field name     CUST    ORDERID  ORDDATE
Record 1        AAA    XZY22    940506
Record 2        BBB    VBF33    940615
Record 3        BBB    VBF44    940617

Step 3A: Convert Numeric to Character Data

Using Define Result Field screen, enter the following:

Field       Expression             Column Heading    Len   Dec
CORDDATE    DIGITS(ORDDATE)

Note: CORDDATE is a field name I made up to hold values from ORDDATE. CORDDATE is a character field with length 6. The value in CORDDATE for record 2 is '940615'. DIGITS is a function provided by Query/400.

Step 3B: Arrange Character Data to Match Date Format

Using Define Result Field screen, enter the following:

Field           Expression                         Column Heading    Len   Dec
SYSORDDAT       SUBSTR(CORDDATE,3,2)||'/'||
               SUBSTR(CORDDATE,5,2)||'/'||
               SUBSTR(CORDDATE,1,2)

Note: SYSORDDAT is a field name I made up to hold rearranged values from CORDDATE. SYSORDDAT is a character field with length 8. The value in SYSORDDAT for record 2 is '06/15/94'. SUBSTR and ││ are functions provided by Query/400.

Step 3C: Create Date Format Field

Using Define Result Field screen, enter the following:

Field       Expression                  Column Heading    Len   Dec
DATEFORM    DATE(SYSORDDAT)

Note: DATEFORM is a field name I made up to hold date formatted field information that was in SYSORDDAT. DATEFORM is a date format (L) field with length 8. DATE is a function provided by Query/400.

Note: If year is before 1940 or after 2039, refer to chapter 5 of the Query/400 Users Guide on how to handle that situation.

Step 4: Set Up Selection Criteria

Using Select Records screen, enter the following:

 AND/OR  Field             Test   Value (Field, Number, 'Characters', or
        DATEFORM          LE     SYSDAT

Note: DATEFROM is a field created in Step 3C and SYSDAT is a field created in Step 2.

Conclusion:

Running the query you wrote following the steps outlined above will display records 1 and 2, if today's system date is June 15, 1994. Because CURRENT(DATE) function was used, you do not need to change the query to use a new date when the query is run again. For example, when you run the query on June 17, 1994 you will display all three records.

This is the result of the query:

CORDDATE          SYSORDDAT         DATEFORM          SYSDAT      CUST        ORDERID     ORDDATE  
0940506   05/06/94   05/06/94   03/15/00   AAA     XZY22     940,506  
0940615   06/15/94   06/15/94   03/15/00   BBB     VBF33     940,615  
0940617   06/17/94   06/17/94   03/15/00   BBB     VBF44     940,617