Focal Point
30 days ago

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

May 06, 2008, 12:36 PM
ColdWhiteMilk
30 days ago
I have:

-SET &TODAY2 = DATECVT(&MDYY,'MDYY','MDYY');

Which gives me today's date.

Now I'm trying to do:

-SET &BACK30DAYS = &TODAY2-30;

Which gives me 5061978 (30 yeara ago).

Does anyone know how to get the value for 30 days ago?


Production - 7.6.4
Sandbox - 7.6.4
May 06, 2008, 12:56 PM
Darin Lee
Use the DATEADD function with a -30 for number of days to add. Don't have my functions book at the moment so I'm not sure of all the parms.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
May 07, 2008, 01:07 AM
Danny-SRL
CWM,
Try:
  
-SET &D30=AYMD(&YYMD, -30, 'I8YYMD');
-TYPE &YYMD &D30



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

May 07, 2008, 04:00 AM
Tony A
CWM,

To add an explanation that might be helpful, variables do NOT have an associated "format" so using DATECVT on the variable &MDYY gives you nothing more than your original &MDYY. Also the resultant value is not a "date" but a number - i.e. 5072008 instead of 05072008. You cannot act on it as you could a database field because there is no concept of a "date" format in a variable. So taking 30 "days" away is just performing a calculation of 5072008 - 30 giving you a numeric result of 5071978 and not a "date".

To get what you want, you will have to do as Darin and Danny suggest and action your date difference on the actual date variable, but remember, that will also give you a number and not a "date" albeit a numeric representation of a date. Also note that Danny used I8YYMD format in AYMD, that is because you cannot use MDYY formats in the AYMD function. If you use MDYY format in DATEADD (-SET &D30 = DATEADD(&MDYY, 'D', -30); ), then you will end up with exactly the same result as using your original method - i.e. 5071978 - a number that looks like a date but the wrong one. For me DATEADD is not the function to use with variables.

So, armed with the knowledge that "date" variables are actually a number and not a date, you therefore understand that 5072008 could be interpretted by the end user as 5th July 2008 or 7th May 2008 but internally it will always be 5 million, 72 thousand and 8!

Your way forward? I would use the AYMD format and adjust your code accordingly within any validation or predicate and remember that you are using a numeric representation of a date and not a real date. I would also recommend always using YYMD farmats for "date" variables.

T

This message has been edited. Last edited by: Tony A,



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 
May 07, 2008, 09:06 AM
jgelona
Actually, this has been covered in several other threads. To use the new date functions (DATEADD, DATEDIF, DATEMOV, etc.) with Dialogue Manager, you have to convert the &variable, ie. &YYMD, to a smart date, apply the desired function, then convert it back to an &variable format. In my case, I have a job that I need to run on the 3rd business day of the month and it has to respect our holiday file. Consider the following where I compute &RUNDT to be the 3rd business of the month.

-SET &RUNDT=DATECVT(DATEADD((DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOM'),'BD',2),'YYMD','I8YYMD');

The innermost DATECVT take &YYMD and converts it to a smart date.
DATEMOV then "moves" the result to the 1st day of the month.
DATEADD then adds 2 business days to the 1st of the month.
The outermost DATECVT converts the smart date back to an I8YYMD format that DM can understand.

I can't take credit for this, someone first posted it, but it has been a life saver.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
May 07, 2008, 09:39 AM
FrankDutch
Don't let us forget to mention the well known book
"almost 1001 Ways to Work with Dates in WebFOCUS" buy it, it is worth the money!




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

May 07, 2008, 11:01 AM
Brian Suter
So once you get the item into a smart date - which is number of days - you can do DAY kind of math ... - 30 days ... or even average 3 days. But since you do not think in number of days, use DATECVT to get it back to a YYMD domain.
-* add 30 days ...
-SET &RUNDT=DATECVT((DATECVT(&YYMD,'I8YYMD','YYMD')+30),'YYMD','I8YYMD');


Brian Suter
VP WebFOCUS Product Development
May 07, 2008, 03:12 PM
RSquared
Unless you have an overriding reason for using DM, why not use a define such as this

ABCD/HMDYYs=HGETC(10, ABCD);
CERT_MINUS180/HMDYYs=HADD(ABCD, 'DAY-OF-YEAR', -30, 10, CERT_MINUS180);


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
May 07, 2008, 04:39 PM
Tony A
... and watch out for the bug in using date range selection with variables.

In 7.6..4, if you have
-SET &FRDATE = '01/05/2008';
-SET &TODATE = '01/06/2008';
.....
WHERE date_field FROM &FRDATE TO &TODATE
.....

You only get data with dates from 01/05/2008 through 31/05/2008.

Quick solution / workaround? Create temporary defined dates to hold &FRDATE and &TODATE and use them in the FROM ... TO

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 
May 08, 2008, 12:02 PM
saikumar
CWM,
Try this,
-SET &30DAYSAGO = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'D',-30)),'YYMD','I8YYMD');

which gives u 30 days back date with format ex: 20080408
If u want the Date in different format create virtuval field with formats available in WebFOCUS.

Ex: 30DBACK/YYMD = &30DAYSAGO;
which gives you date in format 2008/04/08.

Thanks,
Saikumar.


WebFOCUS 8103 Appstudio, Dev Studio MRE
Client Windows 7, Serveron Unix.
Excel, PDF, HTML,AHTML
May 11, 2008, 09:56 PM
Piipster
quote:
Unless you have an overriding reason for using DM, why not use a define such as this

ABCD/HMDYYs=HGETC(10, ABCD);
CERT_MINUS180/HMDYYs=HADD(ABCD, 'DAY-OF-YEAR', -30, 10, CERT_MINUS180);


DM variables get calculated once only. DEFINE fields get calculated once for every row of data that passes initial WHERE statements.

Unless you have an overriding reason for using DEFINEs use DM variables to limit overhead.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
May 11, 2008, 11:10 PM
Waz
I agree with kp.

Only use Define when it is data related, e.g. 30 days prior to DB field.

DM is the way to go.

If you are ever worried about reaching the DM variable limit (1000 approx) use
-SET &VAR =;

Note: No space between the = and ;.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!