I have a fex that is returning the following error:

(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT


Here's my fex...
-SET &Y6MO = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')), 'D', -183)), 'YYMD','HYYMDI');
TABLE FILE JOBDATA
SUM
CNT.DST.BIDID AS 'COUNT,DISTINCT,BIDID'
BY CLIENTID
WHERE BIDOUT GE &Y6MO;


Here's an excerpt from the jobdata master file.
FIELDNAME=BIDOUT, ALIAS=bidout, USAGE=HYYMDI, ACTUAL=HYYMDI,
MISSING=ON, $

I figured a DATECVT to HYYMDI would make it compatible with the BIDOUT field of the same type.

Any ideas?

The following comes to mind:

....date-time literals in the form DT (date-time literal).

eg WHERE TRANSDATE GT DT(2000/01/01 02:57:25)

This may help.

If you -TYPE your &Y6MO variable, you will see that it is not a datetime stamp. If you just need the time to be zeroes, you can set up the amper variable so that it is just a date and then concatenate the time to it and use the second variable in your WHERE clause.

Something like this:

-SET &Y6MO = AYMD(&YYMD,-183,'I8YYMD');
-TYPE &Y6MO
-SET &Y6MODT=&Y6MO | ' 00:00:00.0000';
-TYPE &Y6MODT

You may probably want to use a combination of the techniques provided by both jimster06 and GinnyJakes. I just did and it help me to solve a query problem I was having when trying to retrieve information from Oracle table:

-* Any date to be processed
-DEFAULT &MYSTARTDT=20080701;

-* Go 6 months back (or something like that) and set value as date/time
-SET &MYSTARTDTTM=EDIT(DATECVT(DATEADD(DATECVT(&MYSTARTDT,'I8YYMD','YYMD'), 'D', -183), 'YYMD','A8YYM'), '9999/99/99') | ' 00:00:00';
-TYPE &MYSTARTDTTM

-* Retrieve from DB
TABLE FILE ...
PRINT ...
WHERE DTTM_IN_TABLE GE DT(&VACTSTARTDT);
END