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:
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:
================================== 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.
This was first published in September 2001