I have the following code in a define:
WORK_DATE_YYMD/YYMD = '&DATEYYMD';
LAST_SAT_YYMD/YYMD = DATEMOV(WORK_DATE_YYMD,'EOW') - 6;
WE/YYMD = LAST_SAT_YYMD;
BEG_WK/YYMD = WE - 8;
END_WK/YYMD = WE + 2;

I want the BEG_WK and END_WK to be date-time fields to use in a WHERE clause against a field on my file that is date-time HYYMDs format. Apparently, going the other way doesn't pass the variables thru SQl - making the HYYMDs file field a YYMD field in a define and comparing that to BEG_WK, END_WK.

You don't need to define fields to use the date functions.

This should do it:

-SET &ECHO=ALL;

-SET &DT1 = '&YYMD';
-SET &LAST_SAT_YYMD = 
- DATECVT(( DATEMOV((DATECVT(20061107,'I8YYMD','YYMD')),'EOW')) - 6, 'YYMD','I8YYMD');

-SET &BEG_WK = AYMD(&LAST_SAT_YYMD, -8, 'I8YYMD');
-SET &END_WK = AYMD(&LAST_SAT_YYMD, 2, 'I8YYMD');

-SET &SEL1 = EDIT(&BEG_WK,'9999-99-99') | ' 00:00:00.000';
-SET &SEL2 = EDIT(&END_WK,'9999-99-99') | ' 23:59:59.999';

-TYPE &SEL1 &SEL2

&SEL1 and &SEL2 are the values you can use in the SQL WHERE statement (you may need to remove the milliseconds or make them microseconds .9999 for your DBMS). Put the DM variables in quotes in the SQL statement:

WHERE DB_DATE BETWEEN '&SEL1' AND '&SEL2'

Thanks. I added this coding and it worked great. I didn't know you could use EDIT on an I8 field - always thought it needed to be A8. I put a trace on the run in dev studio and the focus code passed the WHERE statement through within it's internal SQL translator - which it didn't do before. It had just ignored the WHERE since I was using 2 DEFINED date fields to compare. With this coding I use the original file TRAN_DATE of HYYMDs.

-SET &LAST_SAT_YYMD = DATECVT(( DATEMOV((DATECVT(&YYMD,'I8YYMD','YYMD')),'EOW')) - 6, 'YYMD','I8YYMD');
-SET &BEG_WK = AYMD(&LAST_SAT_YYMD, -8, 'I8YYMD');
-SET &END_WK = AYMD(&LAST_SAT_YYMD, 2, 'I8YYMD');

-SET &SEL1 = EDIT(&BEG_WK,'9999/99/99') | ' 00:00:00.000';
-SET &SEL2 = EDIT(&END_WK,'9999/99/99') | ' 23:59:59.999';

-TYPE &SEL1 &SEL2
then put this in my TABLE FILE coding..
WHERE TRAN_DATE GE DT(&SEL1) AND TRAN_DATE LE DT(&SEL2);

Glad it worked out.