Looking for a good date for Query/400?

• Malcolm Westley
• Published: 07 Sep 2001

Query/400 has many useful date functions. Unfortunately, they do not work with the 7 or 8 digit numeric fields often used to store dates in files. Luckily, Query/400 also includes two functions that can be used to transform these reluctant dates into something Query/400 will dance with.

First, define an intermediate field (called DT in this example) using the DIGITS function on the numeric date field:

FIELD EXPRESSION
DT DIGITS(numeric date field)

DT will be a character representation of the numeric date field.

Next, use the SUBSTR function to change the intermediate field into the job date format (in this example MDY from a 7 digit number CYYMMDD):

 FIELD    EXPRESSION
DATEMDY  SUBSTR(DT,4,2) || '/' || 
         SUBSTR(DT,6,2) || '/' ||
         SUBSTR(DT,2,2) 

The field DATEMDY will now be correctly interpreted by Query/400 as a date field. If DATEMDY contained someone's hire date, the following expression will return the number of years worked:

YEAR(CURRENT(DATE)-DATE(DATEMDY))

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.