I am trying to create a define field that always has the current date so that I can use it in a where statement to say filter on trans_date where trans_date < Today's Date and tran_date >(Today's Date - 36 months). Is the Today (output) function the one to use or is there another one? I am getting errors when I check the define statement. Help anyone?

G'Day Leah,

Try TODAY/DMYY = '&DATEDMYY'. It works for me.

Cheers

Andrew W

Leah,If your transdate is a smart date,
then you want to translate the system date &YYMD (which is 20040816) into a smart date.
You can do it in dialog manager
-SET &SMARTDAY = DAYMD(&YYMD,'YYMD');
so smartday is now 38214, which is a smartdate
and do a direct comparison in a filter
IF TRANSDATE GT &SMARTDAY
Point is, you gotta get both dates into the same format.(you know this already, right?)
Or you can do it in a define
TODAY/YYMD WITH somevar = &YYMD ;
or it might be that your define is bombing because it doesn't have the WITH varname, which you need because you're defining a constant.
Are we helping at all?

How do I create a field on the report which shows the current date? I want to use a function to that. The problem I am having is where to define the beginning date and end date of my filter.

Leah,

If you just want a date printed such as 08/17/04 as part of the heading, you can just put the &DATE in your heading line.

"Reported on &DATE"

Leah Cross

I need to be able to pull in the system date (current date) and put it in a function (system date-36 months) and then use both the system date and the function result in a where statement to create a filter. Where can I pull the system date from into a function?

I have created a define for the current_date using
DEFINE FILE filename
TODAY/YYMD = '&YYMD';
Now I want to pull this into a where statement to filter on this created field. I am trying to say pull trans_dte where trans_dte less than current_date. I am getting an error message when I run the report saying that current_date is not a recognized field. Does any one know what step I would be missing? What is the correct way to pull in a field created in a define into a where statement?

G'Day Leah Bell,

I'm getting a little confused but I'll try to help.

The TODAY/YYMD='&DATEYYMD' is the current date (presumably the system date is the current date). To get the date 36 months before the current date try another define like PASTDATE/YYMD = DATEADD(TODAY, 'M', -36).

Then your WHERE statement will be filtering on records between PASTDATE and TODAY.

I hope this helps.

Cheers

Andrew W

Leah Bell,

Here is some syntax that I use. &WHERE_SDATE and &WHERE_EDATE contain the values you appear to be looking for. I like to use Dialog Manager for things like this, this way I am always sure that the where gets passed to the RDBMS.

-SET &SDATE=&YYMD;
-SET &SDATEY=EDIT(&SDATE,'9999$$$$');
-SET &SDATEM=EDIT(&SDATE,'$$$$99$$');
-SET &SDATED=EDIT(&SDATE,'$$$$$$99');
-SET &CURMTHYR = &SDATEY | &SDATEM;
-SET &WHERESDATE = AYM(&CURMTHYR, -36 ,'I6');
-SET &WHERE_SDATE = &WHERESDATE | &SDATED;
-SET &WHERE_EDATE = &YYMD;
-RUN
-* Remove the -* below to type these values back to the screen (report).
-*-TYPE STARTDATE = &WHERE_SDATE ENDDATE = &WHERE_EDATE
-*-RUN
-*-EXIT