Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Calculating Date Range

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Calculating Date Range
 Login/Join
 
<BrianS>
posted
I am trying to automate a query so that the user does not have to manually enter the date range every time it is run. I'm needing something like the following:

From Date = Today's date - 7 days
To Date = Today's date - 1 day

So basically I'm needing the query to run for the previous week's date range. Any help, as always, is greatly appreciated. Thanks.
 
Report This Post
Leah
Virtuoso
posted Hide Post
Is the data in 'smart date' format in your master file definition? If so, then you just define the two dates you want for the range and do the selection on the date and select based on the two dates. An example

  
TODAYS/YYMD = &YYMD;
YESTERDAY/YYMD = TODAYS - 1;
LASTRUN1/YYMD = TODAYS - 7;


One issue on the use of defined fields in selection though is unless you limit the 'answer set' with other criteria, your whole file will come back then the dates. I'm sure the dialog manager gurus have another method as well.

Good luck.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
FrankDutch
Virtuoso
posted Hide Post
Brian

Please start with updating your signature,so we know what version you have.

Do you have studied the manuals and special the DM part?
How about the amper values?

You have a basic date that holds today "&YYMD"

-SET &START=DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -7);
-SET &END=DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1);




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
Leah and Brian,

In Dialogue Manager, you would use the AYMD subroutine.
-SET &TODATE=AYMD(&YYMD,-1,'I8YYMD');
-SET &FRDATE=AYMD(&YYMD,-7,'I8YYMD');


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
DATEADD cannot be used with DM, use AYMD as Ginny states, but in DM the output format should only be I8, not I8YYMD (which is a picky point I know.)


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
Alan,

According to the Functions manual, the format can be I6, I6YMD, I8, or I8YYMD.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<BrianS>
posted
Got it. Thanks everybody.
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
Brian

Ginny was right, but it is interesting for you to see the difference in result.

-SET &ECHO=ALL;
-SET &START=DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')), D, -7);
-SET &EIND=DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')), D, -1);
-SET &TODATE=AYMD(&YYMD,-1,'I8YYMD');
-SET &FRDATE=AYMD(&YYMD,-7,'I8YYMD');
TYPE &START
TYPE &FRDATE
TYPE &EIND
TYPE &TODATE


So try this.

The "start" end "eind" wil give a number that represents the date, can be usefull.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Tony A
Expert
posted Hide Post
quote:
DATEADD cannot be used with DM
Not quite right Alan, or so it appears.

Works fine on both set-ups here.

-SET &MBEG=DATECVT(DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -7), YYMD, I8YYMD);
-SET &MEND=DATECVT(DATEADD((DATECVT(&YYMD, I8YYMD, YYMD)), D, -1), YYMD, I8YYMD);
-? &M

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Alan B
Virtuoso
posted Hide Post
Tony, I was quoting the documentation here
quote:
The DATEADD function cannot be used with Dialogue Manager. DATEADD requires dates to be in date format; Dialogue Manager interprets a date as alphanumeric or numeric.
. So yes, if you want to convert to and fro with DATECVT, it will work. But AYMD is simpler.

As for the formats for the return in AYMD, the I8YYMD is not really a valid &variable format, as it would be for a DEFINEd or COMPUTEd field.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Tony A
Expert
posted Hide Post
Alan, accepted. I don't have a manual readily to hand and so did not check Frowner What you say makes sense though.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
jgelona
Master
posted Hide Post
IMO, it would be a good addition to the manual to show how to use any of the date functions in Dialogue Manager. I always use AYMD if I'm just wanting to add or subtract days from a date, however, one has to use DATEADD with DATECVT to add Business Days or Week Days to an & variable that is a date in DM.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Calculating Date Range

Copyright © 1996-2020 Information Builders