Does anyone have any suggestions on the following --
I have a FOCUS table which has field
endng_dt I8MDYY
once a month I delete records from this table using the REBUILD. I set variable
-SET &E_DT = 11012004;
Here is the rebuild code--
REBUILD
REBUILD
DYS_MV
YES
ENDNG_DT GT &E_DT,$
-RUN

what I overlooked was the date in the focus table is in MDYY format and the variable I defined is also in MDYY format so the statment
02052005 > 11012004 is considered false because it views it as a alpha filed and 02 is not greater than 11. I want to avoid changing the format in the Focus table. Any thougths on how I can temporarily convert both dates to YYMD so comparison is true.

If you absolutely must keep the field in I8MDYY format, I can see two ways around this.
1. Cleaner - Delete all records meeting your selection before performing the REBUILD.
2. Messier - Have a copy of the date field in I8YYMD format, on which you base can your rebuild selection.

I haven't worked with a FOCUS DB in a long time, but, would it be possible to add a DEFINEd field in the Master and base it on the MDYY field?

I'm not sure if the REBUILD condition will work on the DEFINEd field, but it's worth a try.

If Rebuild allowed screening on a defined field, you could insert the DEFINE in the MFD
DEFINE END_DATE/YYMD=E_DT;$
and screen on that
END_DATE GE '&A_YYMD_VALUE',$

-- but my recollection is that it must be a real field, in which case you're stuck.

An alternative is to delete the records whose dates are out of range (using MODIFY), and then do a simple REBUILD without conditions.

-SET &SMARTTODAY = DATECVT(&E_DT, 'I8','MDYY');
this ought to convert your &var input 11012004 into a smartdate.
see it that helps, and let us know.
otherwise, robin's solution might be the one that gets you home for dinner.

---oh, i see now, jack. i had thought her db field was smart. i see now it isn't. eww.
cheri, i think we'ld all agree that i would be worth your while to recreate your db with a date format that was orderable, either i8yymd or smart. would avoid this problem in the future.

quote:

Originally posted by susannah:
[qb] -SET &SMARTTODAY = DATECVT(&E_DT, 'I8','MDYY');
this ought to convert your &var input 11012004 into a smartdate.
see it that helps, and let us know.
otherwise, robin's solution might be the one that gets you home for dinner. [/qb]

Whatever value that would place in the &var, the condition in the REBUILD still compares it to an I8 field containing a +mmddyyyy value, so that's not a solution.

Another possible tack: Replace the real I8MDYY field with a smart date field of another name, and add back the original name as a define. Depending on several factors, that might have no impact on reporting, and minimal, localised impact on data maintenance.

Jack -
not sure what you mean by --
Another possible tack: Replace the real I8MDYY field with a smart date field of another name, and add back the original name as a define.

do this in the master file descritpion ?

It would be change to the app -- requiring a revision of the MFD, plus a one-time change to the data files.

Assuming the date field is not indexed, it should have no impact on file size (since dumb and smart dates are both ACTUAL=I4) or reporting logic (since the old fieldname still yields the old i8mdyy value), and would require only minimal change to the existing data maintenance (MODIFY or MAINTAIN) programs -- but it enables the REBUILD to discard aged records.