>I have a field that was previously not being used as a real date field but now needs to be a type date. When we changed the data type of the field, type() returns D but it doesn't order like a date and SQL querys on that field are unpredictable for the records that were originally entered as type C. Any way to fix the old records to be a real date?
Easiest would be a SQL Select that extracted the CTOD() of the field with the same name to a temporary table or SDF text file (if you extract to a text file, save the DTOS(CTOD()) of the field being changed), alter the structure using ALTER TABLE or MODIFY STRUCTURE or whatever, delete the old data and append in the temporary copy.
You might do better creating a new table appending and renaming rather than the DELTE/APPEND routine - brave souls who don't often are paid a visit by Mr. Murphy. if you COPY STRUCUTRE WITH PRODUCTION to the temporary table, change its structure, and then append, you gain the benefit of having the old data intact and getting your structural CDX rebuilt with no bloat; it also can clean up your memo files at the same time, and you can decide not to copy over deleted records, too.