Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Indexing DATE fields?
Message
De
19/08/1999 04:27:36
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
 
 
À
18/08/1999 22:41:47
Peter Brama
West Pointe Enterprises
Detroit, Michigan, États-Unis
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00255114
Message ID:
00255162
Vues:
13
>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?

Yes and no.
No if it's the only expression in index than you don't need to do dtos() conversion (ie: index on dBirthDate tag Birthdate). But it doesn't harm if you do.
Or if expression is something doing date math. ie: You have to date fields dInvoiced and dPaid. You want to see them in the order with most aged first. Then you do date math but no dtos() conversion :
index on dPaid - dInvoiced tag Aged descending

Yes, if you want to combine your datefield with any character expression. Dtos() returns date as a char string in YYYYMMDD format and is independent of date settings. It truely sorts. Suppose you have a parent table that links to a child via cust_id field. Cust_id could be char, numeric. You want to see child records ordered by dVisitDate showing last visit first. You have a need to include cust_id as the first field in index so that your relation would work :
If cust_id is char - index on cust_id+dtos(dVisitDate) tag RelTag descending
If cust_id is numeric you should convert it to char also. There how you would convert to char is up to you and also relation should do the same conversion - index on str(cust_id,6)+dtos(dVisitDate) tag RelTag descending
Parent relational expression :
set relation to str(parentcust_id,6) into child

PS: DTOS() returns the date as it's stored on disk ;) ie: 08/17/99 is stored as 19990817 on disk and dtos({08/17/99}) returns "19990817". While your usage of date as {08/17/99} depends on your date settings, its storage doesn't, and compatible with any date setting. That means your date would read correctly on a nonUS installation too. That's the main reason why all Fox versions are Y2K compatible in data storage (thanks to Fox software).
Cetin
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform