IBM DB2 for i: Tips

Query/400 files based on date

Description

Using Query/400 extract data from a file based on todays date without having to change the query every day.

Action

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.

General steps

Note: Read chapter 5 of Query User's guide for background on this process publication number is SC41-9614-02.

Specific instructions for each step

Step 1

Determine DATE format.

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

Job date . . . . . . . . 061594        Date, *SAME
Date format  . . . . . . *MDY          *SAME, *SYSVAL, *YMD, *M
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 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 thru C, we will describe the sample database used in the illustration.

OPEN ORDER FILE
Field name     CUST    ORDERID  ORDDATE
Record 1        AAA    XZY22    0940506
Record 2        BBB    VBF33    0940615
Record 3        BBB    VBF44    0940617

Fields CUST and ORDERID are defined as character. Field ORDDATE is defined as numeric 7 positions with 0 decimal. The meaning of the digits (reading left to right) in field ORDDATE are century (1 digit), year (2 digits), month (2 digits) and day (2 digits.) The field could be thought of as being in the form CYYMMDD. Steps III.A to III.C will show you how to convert the CYYMMDD form to MM/DD/YY date format.

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 7. The value in CORDDATE for record 2 is '0940615'. 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,4,2)||'/'||
SUBSTR(CORDDATE,6,2)||'/'||

SUBSTR(CORDDATE,2,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(CORDDATE)

Note: DATEFORM is a field name I made up to hold date formatted field information that was in CORDDATE. 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 3 records.

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

IBM Power Systems hardware is at the heart of enabling major business and industry transformations fueled by the explosive growth of big data and analytics. Power Systems help organizations drive faster business insights with industry leading performance, scalability and virtualization flexibility.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

IBM's integrated approach to developing Systems and Systems Software stacks together delivers a platform that supports next generation applications for big data and analytics and cognitive computing on an open platform for choice while providing maximum utilization, availability, and flexibility to help you deliver new advantages to your business.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.


Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information