ok, this definitely has to fall into the "WTF" category...

I'm trying to subtract 7 days from today in my code (creating a defined field). After trying everything I could think of, I found the following function:

DATEADD(YYMDdate, 'unit', #units)

so I did

PROCDATE/YYMD=DATEADD(&YYMD, 'D', -7)

Why am I getting 4/98/04 for a result? I thought WebFocus used Smart Dates? It works if I ADD 7 days, but I want to SUBTRACT!

ARGHHHH!!!!

Jen

If it's not Dialog manager, try this:

COMPUTE AA/YYMD = '&DATEYYMD';
COMPUTE PROCDATE/YYMD=DATEADD(AA, 'D', -7);

I tried it in DM, it doesn't work:

-SET &PROCDATE = DATEADD(&YYMD, 'D', -7);

gives: 20040498

It only works for +7 because we're at the beginning of the month and +7 doesn't bring you to the next month, try

-SET &PROCDATE = DATEADD(&YYMD, 'D', 30);

Date subroutines are generally used for old date formats (A8MDYY). With new dates (MDYY) you should just be able to add and subtract days. have you tried this?

TODAY/MDYY = &MDYY;
LASTWEEK/MDYY = TODAY -7;

That was the first thing I tried, but it subtracts 7 from the year, not the month.

It needs to be a 2 step process:

DEFINE FILE xxxx
CURDATE/YYMD=&YYMD;
NEWDATE/YYMD=DATEADD(CURDATE,'D',-7);
END

or

DEFINE FILE xxxx
DT2/YYMD=DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-7);
END

The reason for this is that dialog manager doesn't understand smart dates, it only works with alphanumeric fields. &YYMD returns the alphanumeric value 20040505 which needs to be converted to a smart date.

You don't need the DATEADD subroutine in a DEFINE. What is the format of your field and what release are you running. I did the follwoing.

DEFINE FILE CAR
TODAY/MDYY WITH COUNTRY = &MDYY;
LASTWEEK/MDYY = TODAY - 7;
END
TABLE FILE CAR
PRINT CAR TODAY LASTWEEK
BY COUNTRY
END

And got this.

COUNTRY CAR TODAY LASTWEEK
ENGLAND JAGUAR 05/06/2004 04/29/2004
JENSEN 05/06/2004 04/29/2004
TRIUMPH 05/06/2004 04/29/200

That works if I am just printing the field. But what I'm trying to do is then filter on that field LASTWEEK, by using it in a where clause.

For example:

DEFINE FILE TRANSACTIONS
TODAY/MDYY WITH CUSIP=&MDYY;
LASTWEEK/MDYY=TODAY - 7;
END
TABLE FILE TRANSACTIONS
SUM stuff
BY stuff
WHERE (TRADE_DATE GE LASTWEEK)
END

I'm running against a SQL table. TRADE_DATE is a smart date with format YYMD.

First, using Dialogue Manager and AYMD function, get the &LASTWEEK variable.
After, use the variable in your IF clause.


-SET &LASTWEEK = AYMD(&YMMD, -7, 'I8');
TABLE FILE TRANSACTIONS
SUM stuff
BY stuff
IF TRADE_DATE GE &LASTWEEK
END


Regards,
Mikel

This message has been edited. Last edited by: <Mabel>,

BINGO!!

Thanks, Mikel! And thanks everyone who replied!

Jen