Article ID: 253595 - View products that this article applies to.
This article was previously published under Q253595
This article has been archived. It is offered "as is" and will no longer be updated.
Sometimes there is a need to create indexes using DateTime fields along with other fields in your table. To index on multiple fields of different data types, it is best to convert each non-character field value into a character value using the appropriate conversion function. The function TTOC() provides an indexable character value from a DATETIME value when used with the second parameter value of 1. This article provides examples of how to use this function to create various indexes using a DateTime field.
By default, indexes are created in ascending order. If you include the DESCENDING keyword, the index is in descending order. Sometimes you might need to build an index on a field in ascending sequence and a second field in descending sequence. To achieve this result, you must calculate a value for the second field that decreases in value as the actual value increases. For a DateTime field, if you subtract the numeric value of the character value returned by the TTOC() function from the number 99999999999999, you obtain a value that decreases as the actual value increases. By converting this calculated value to a character value, you can combine this with another character value to achieve the desired results.
The TTOC() function, with the second parameter having the value 1, returns a character value from a DATETIME value in the format YYYYMMDDhhmmss. If you enter the following statement in the Command window, you see the current system date and time presented in this format:
To see examples of various indexes with an integer field and a DateTime field, create a program called EXAMPLE.prg and paste the following code into the file:
The above program creates a table consisting of an Integer field, a DateTime field, and a Character field. It then adds five records to the table. The program then creates five index tags for the following situations:
For additional information on indexing, click the article number below to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/97066/EN-US/ )How to Index a Combined Date and Character Field
Article ID: 253595 - Last Review: February 28, 2014 - Revision: 2.2