>>>DTOS converts a date to a string in the format yyyymmdd, so it does sort correctly. You only have to convert numeric values (including dates) to strings for indexing when the index expression combines it with a string -- since you can concatenate a string only with another string. (For example, if you're indexing by customer ID and date.)
>>>
>>>
>>>>I don't remember where or why, but it was suggested that to index a date field in the expression put DTOS(datefieldname). Is that right? Why? Wouldn't that equate today 08/17/99 to "081799" which won't sort correctly?
>>
>>PMFJI: If you are not using a concatenated key with a character string or need to seek using a character string, why not just use index on (datefield) tag (datefield)... Any function built into a tag expression will inevitably make it somewhat slower. DTOS() preserves the order to match a pure index on (datefield), as stated above -- so ordering is not an issue. If you do a SQL select and need to order by the (datefield) you can still keep it optimizable even if its "...order by name, datefield" if the tag is a date tag. Else, your optimization needs to use DTOS(datefield) as the ordering/seeking criteria...
>>
>>HTH
>>
>>Rob
>
>I use the DTOS() function on compound index expressions and they work fine for me. you can use the DTOC() function too but the order won't be the same as DTOC handle the date data as a text unless you send specifie a parameter of 1, so:
>
> With DTOC()
> {08/19/99} = "08/19/99"
>
> With DTOC(date_expr,1)
> {08/19/99} = "19990819"
>
> With DTOS()
> {08/19/99} = "19990819"
>
> DTOS( )isn’t affected by SET DATE or SET CENTURY.

Hi Emmanual:

You are correct. In fact, one of the biggest Y2K bug issues we had at my employer was the really stupid use of DTOC() in index tags combined with CENTURY OFF. In the first place, as said above, DTOC() with out the 2nd parameter just puts all the 'august' dates together regardless of year, so it blows any meaningful searching, plus, you cannot just SET CENTURY ON to solve any Y2K issues, as you also have to re-generate the index to have it use the full 4-digits. I can't believe how such a stupid index tag definition could ever have been used, but it was built in to so much stuff that it created significant problems with just changing relations and seeks to use proper key values after century was set correctly....

Thanx for your response.

Rob