Midrange Information, Tips and Techniques
by Brian Coy
Sorting a date in Query:
If you have a date field that needs to be sorted down, or you need to select records based on its criteria, the date field will need to be in a year, month, day format. Most likely the field will be in a month, day, year format--which will need to be converted. This can be done easily by defining a couple of fields in your query definition.
Let us say, for example, you have a numeric field called DATOPN, and it is in (MMDDYY) format. You must translate the date from calendar format (MMDDYY) to (YYMMDD). Query recognizes only numeric values, so the untranslated date 12-01-84 (120184) would be considered greater than the date 10-08-97 (100897). With the following manipulation, the year is first, so 12-01-84 is (841201) and 10-08-97 is (971008). This will make it much easier to select accounts based on their open date.
Below is an example of the "Define Result Fields" in the Query Definition:
Define Result Fields
Type definitions using field names or constants and operators, press Enter.
Operators: +, -, *, /, SUBSTR, ||, DATE...
Field Expression Column Heading Len Dec
CHARDATE digits(DATOPN)
OPENYYMMDD substr(CHARDATE,6,2) || Date Open by Year
substr(CHARDATE,2,2) ||
substr(CHARDATE,4,2)
Bottom
*Note: The digits function under the Expression column returns a character string representation of a number so that an operation such as | | or SUBSTR can be performed.
A:\TIP9705.DOC