Contact Manager - Indexing on Dates

Indexing on a Date which is Located in a Character Field:

If you have a character field which contains a date, and use that field in an index, you may not get the intended result. For example, if one record contains the date December 10th, 1996 and another November 10th, 1998, the records would be sorted as follows:

11/10/1998
12/10/1996

This occurs because TeleMagic is sorting first by month, then by day and year. To properly sort your records by date, you must index on the year first, followed by the month and day. In the following examples we’ll use the TMLASTREV field, and assume that all dates use the format MM/DD/YYYY:

CTOD(SUBSTR(contact1.tmlastrev,3,10))
This expression uses the CTOD() function to convert the date portion of the TMLASTREV field to a date. Date fields and date expressions are automatically sorted first by the year, but they cannot be combined with a character expression.

SUBSTR(contact1.tmlastrev,9,4)+SUBSTR(contact1.tmlastrev,3,5)
DTOS(CTOD(SUBSTR(contact1.tmlastrev,3,10)))
These two expressions use different methods to accomplish the same thing, which is to sort first by year, then by month, and finally by date. Both expressions can be combined with other character expressions.

Indexing on a Date Field: To index on a date field alone, you need only enter the field name in the index expression:

contact1.datefield

However if you need to combine a date field with character fields, you will have to convert the date field to a character expression. This can be accomplished by using the DTOC() function, but the result would be records sorted first by the month, as shown above. To convert the date field to a character expression, and properly sort first by the year, use the DTOS() function:

DTOS(contact1.datefield)

 

-- End of FAQ #21

FAQ ID #21

Product Related: TeleMagic 4.x

Last Updated:
March 19, 2000