Focal Point
[SOLVED] Substituting the start and end dates in a procedure

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

June 12, 2006, 05:39 AM
Adolf Mergulhao
[SOLVED] Substituting the start and end dates in a procedure
I need to generate the start and end dates form the system dates. The reason is to use Report Caster to generate reports at the beginning of the month.
The query
TABLE FILE RFIFLTREC
PRINT FR_OOS_DUR
FR_CLOSE_DATE
FR_CLRCAT
FR_DIGITAL
WHERE FR_CLOSE_DATE GE '&SELFROM' AND FR_CLOSE_DATE LT '&SELTO'
END

The close date is A8( i.e. 20060501) format in the master file.
The requirement is to look at the system date on the 5th of the month i.e 20060605 and get the system to generate 20060501 as start date and 20060531 as the end date

Any ideas

This message has been edited. Last edited by: Kerry,


Webfocus 7.6.2 on Windows Enterprise 2003 Server and
Webfocus 7.6.2 on HP Unix Server
June 12, 2006, 06:45 AM
Tony A
Hi Adolf,

Quick off the top of the head suggestion would be to use the DATEMOV function, something like -

-SET &BegDate = DATEMOV(&YYMD, 'BOM', 'A8YYMD');
-SET &EndDate = DATEMOV(&YYMD, 'EOM', 'A8YYMD');


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 
June 12, 2006, 08:19 AM
ET
I think that the datemov function only works with smart dates. If you always want the first and last calendar days of the previous month based on the current system date, this will probably work for you.

The following code:

-SET &BEGCURR = EDIT(&YYMD,'99999901');
-SET &ENDDATE = AYMD( &BEGCURR, -1 , 'I8YYMD');
-SET &BEGDATE = EDIT( &ENDDATE,'99999901');
-TYPE &BEGDATE &ENDDATE

Yields this result.

20060501 20060531


FOCUS 7.6 MVS PDF,HTML,EXCEL
June 12, 2006, 10:14 AM
Prarie
This works too.
DEFINE FILE CAR
TODAY/MDYY = '&DATEMDYY';
BEG_CUR_MO/MDYY=DATEMOV(TODAY,'BOM');
END_CUR_MO/MDYY=DATEMOV(TODAY,'EOM');
BEG_PRIOR_MO/MDYY=DATEADD(BEG_CUR_MO,'M',-1);
END_PRIOR_MO/MDYY=DATEMOV(BEG_PRIOR_MO, 'EOM');
END
TABLE FILE CAR
PRINT CAR NOPRINT
TODAY
BEG_CUR_MO
END_CUR_MO
BEG_PRIOR_MO
END_PRIOR_MO
END


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
So does this:

-SET &MTDF = DATECVT( DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOM'),'YYMD','I8YYMD');

The innermost DATECVT function call converts the integer value &YYMD to a smart date. Then the DATEMOV function runs and then the outermost DATECVT function converts the smart date back to an integer.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
FYI,

The issue has been resolved on Adolf's side, so many thanks to everyone's input. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
I ended up going for PBrightwell's solution. It works like a charm.

Thank you everybody for your help.

Rafael

BTW - I ordered the 1001 ways to work with dates book. This seemingly simple stuff was driving me crazy.