>>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.
I'm a mixture of Albert Einstein and Arnold Schwarzenegger. The only trouble is that I got Einstein's body and Schwarzenegger's brain