Focal Point
Calculating Date Range

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8751020072

February 05, 2008, 02:59 PM
<BrianS>
Calculating Date Range
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.
February 05, 2008, 03:15 PM
Leah
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
February 05, 2008, 03:25 PM
FrankDutch
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

February 05, 2008, 04:33 PM
GinnyJakes
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
February 05, 2008, 04:54 PM
Alan B
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
February 05, 2008, 05:11 PM
GinnyJakes
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
February 05, 2008, 05:45 PM
<BrianS>
Got it. Thanks everybody.
February 06, 2008, 03:44 AM
FrankDutch
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

February 06, 2008, 04:05 AM
Tony A
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 
February 06, 2008, 05:25 AM
Alan B
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
February 06, 2008, 05:38 AM
Tony A
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 
February 06, 2008, 09:50 AM
jgelona
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.