I'm trying to subtract 1 day from the current day and use it in a where clause. I'm confused on the format. The ENTDATE and DATE define fields work but I can't get the DAYAGO define to work. I also tried using the AYMD function. Can someone please assist.

DEFINE FILE INFO 
ENTDATE/HMDYY=ENTRYDATE
DATE/A10=TODAY(DATE);
DAYAGO/MDYY=DATEADD(DATE, 'D', -1);
END
TABLE FILE INFO
NAME
ADDRESS     
ENTDATE
WHERE ENTDATE EQ DAYAGO;

This message has been edited. Last edited by: kitten,

For starters, 1) add a semicolon at the end of this line: "ENTDATE/HMDYY=ENTRYDATE", 2) Add "DAYAGO" to your print line, 3) remove the WHERE statement, 4) Add a RECORDLIMIT and END, and see if "DAYAGO" is what you expect it to be, 5 take from there. and

Oops. I did have the semicolon at the end just didn't copy it over. I also tried to print the DAYAGO field but get the following error:
INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION DATEADD.

Should I have used the AYMD function?

Change DATE/A10=TODAY(DATE);
to
DATE/YYMD = '&DATEYYMD.EVAL';

The function requires a smart date...

quote:

DATE/YYMD = '&DATEYYMD.EVAL';

That worked Tom, but the where clause doesn't. I get the following error: (FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT

Do I need to convert the ENTDATE to Alpha?

Change:ENTDATE/HMDYY=ENTRYDATE;
To: ENTDATE/MDYY=ENTRYDATE;

For the WHERE test, need to have the same format; apples-to-apples, oranges-to-oranges, etc...

I can't change the ENTDATE to MDYY, because it has a date-time format when I remove the H it doesn't work.

quote:

Originally posted by kitten:
I can't change the ENTDATE to MDYY, because it has a date-time format when I remove the H it doesn't work.

Use the HDATE function to get the datetime field into a simple date.

ENTDATE/MDYY=HDATE((ENTRYDATE,'MDYY');

Using a DEFINE for a screening condition is not efficient.

As an example, and I am using an HYYMDs field here, if you are using a different format change the expressions:

-SET &YESTERDAY    = AYMD(&YYMD,-1,'I8');
-SET &YESTERDAY    = EDIT(&YESTERDAY,'9999/99/99');
 TABLE FILE GENERATED
 PRINT *
 WHERE GENERATED_TIMESTAMP FROM DT(&YESTERDAY 00:00:00) TO DT(&YESTERDAY 23:59:59);
 END

Thanks Tom and ND it works. However I experimented with Alan's solution for efficiency and it also worked. Thanks Alan for the efficiency tip!!!