Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Indexing DATE fields?
Message
From
19/08/1999 04:27:36
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
 
To
18/08/1999 22:41:47
Peter Brama
West Pointe Enterprises
Detroit, Michigan, United States
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00255114
Message ID:
00255162
Views:
12
>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
Previous
Reply
Map
View

Click here to load this message in the networking platform