Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Today (output) function in Define
Go
New
Search
Notify
Tools
Reply
  
Today (output) function in Define
 Login/Join
 
<Leah Bell>
posted
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?
 
Reply With QuoteReport This Post
Member
posted Hide Post
G'Day Leah,

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

Cheers

Andrew W
 
Posts: 27 | Location: Sydney, Australia | Registered: May 27, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
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?
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<Leah Bell>
posted
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.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
<Leah Bell>
posted
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?
 
Reply With QuoteReport This Post
<Leah Bell>
posted
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?
 
Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 27 | Location: Sydney, Australia | Registered: May 27, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Leah Bell,

Cool 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
 
Posts: 862 | Registered: May 24, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Today (output) function in Define

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.