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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Adding Dates
 Login/Join
 
<Umar Farook S>
posted
Hi,

I need to add a day to my date.
I have a condition like
Trade date between &start date and &end date.
So Trade date = &start date.
Start date and end date are entered by users.
I have also got payment date which is a user defined field.
Where payment date will be the next day of the Trade date.
If Trade date is Monday payment date will be Tuesday
If Trade date is Tuesday payment date will be Wednesday.....etc etc
But if Trade date is Friday payment date should be Monday

can anyone plesae help me out
 
Report This Post
Virtuoso
posted Hide Post
We have a situation in our admissions letters where we do a date calculation to put a specific date in the letter. Here is the code snippit.

TODAYD/MDYY WITH MM005_MMAP = &LETDATE;
DAYWEEK/WT = TODAYD;
LETTDATE/MtrDYY WITH MM005_MMAP = IF DAYWEEK EQ 'FRI' THEN TODAYD + 3
ELSE IF DAYWEEK EQ 'SAT' THEN TODAY + 2 ELSE TODAYD + 1;
LETTDATM/MtrDYY WITH MM005_MMAP = IF DAYWEEK EQ 'FRI' THEN TODAYD + 11
ELSE IF DAYWEEK EQ 'SAT' THEN TODAYD + 10 ELSE TODAYD + 8;


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Umar Farook S>
posted
Hi Leah
can you please give me a little explanation how your code works.
 
Report This Post
<Umar Farook S>
posted
Please also let me know about the formats of your date &LETDATE
 
Report This Post
Virtuoso
posted Hide Post
quote:
TODAYD/MDYY WITH MM005_MMAP = &LETDATE;
DAYWEEK/WT = TODAYD;
LETTDATE/MtrDYY WITH MM005_MMAP = IF DAYWEEK EQ 'FRI' THEN TODAYD + 3
ELSE IF DAYWEEK EQ 'SAT' THEN TODAY + 2 ELSE TODAYD + 1;
LETTDATM/MtrDYY WITH MM005_MMAP = IF DAYWEEK EQ 'FRI' THEN TODAYD + 11
ELSE IF DAYWEEK EQ 'SAT' THEN TODAYD + 10 ELSE TODAYD + 8;



&LETDATE is passed as mmddyyyy - 07202007 for example

TODAYD is created as a smart date from the parm, then the day of the week is capture in DAYWEEK using the WT format, then I just check the value for the day of the week to create the LETTDATE which is in the form of July 20, 2007 or later depending on the day the job is running.

Need more let me know.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Umar Farook S>
posted
Hi Leah,
i will try this and let you know

What does LETTDATM do?
 
Report This Post
<Umar Farook S>
posted
Hi leah,

I am getting an error when i included the code in
DEFINE FILE ASSET_HOLD4
TODAYD/MDYY WITH MM005_MMAP = &STARTDATE;
DAYWEEK/WT = TODAYD;
LETTDATE/MtrDYY WITH MM005_MMAP = IF DAYWEEK EQ 'FRI' THEN TODAYD + 3
ELSE IF DAYWEEK EQ 'SAT' THEN TODAY + 2 ELSE TODAYD + 1;
END

Error
0 ERROR AT OR NEAR LINE 453 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: MM005_MMAP
0 ERROR AT OR NEAR LINE 455 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TODAYD
0 ERROR AT OR NEAR LINE 457 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: MM005_MMAP
0 ERROR AT OR NEAR LINE 459 IN PROCEDURE MEMFEX FOCEXEC *
(FOC252) COMPUTATIONAL EXPRESSION NOT RECOGNIZED: ELSE IF DAYWEEK EQ 'SAT' THEN
TODAY + 2 ELSE TODAYD + 1
0 ERROR AT OR NEAR LINE 489 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: LETTDATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
 
Report This Post
Virtuoso
posted Hide Post
Replace MM005_MMAP with a field from your data, that is a field in our file. WITH tells focus when to do the define, you could take it out.

If I understand you though in your original, I think you may want to replace startdate with trade date. Also if you just want to add 1 day you don't need quite that much code unless you want to tie it to which day. It was meant to be an example of manipulating dates.

If I may ask, have you had any official training?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
In this instance, we're just calculating the date to print on a report, so we make it 'pretty'.


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

there is also a nice formula that moves the date to a specific other date

DEFINE FILE whatever
NBD/YYMD = DATEMOV(NEW_DATE, 'NBD');
END

This moves the actual date to the next business day.
You have to define your business days before but I suppose you did so.
You can also install a holiday calendar, so for example if you work on Friday and the next Monday is a holiday (not working day) you should not move to Monday but to Thursday .




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
<Umar Farook S>
posted
Hi,

I am trying to add days to my date
Define File
F_START_DT/A8 = EDIT('&STARTDATE','$$$$$$9999') || EDIT('&STARTDATE','99$$$$$$$$') || EDIT('&STARTDATE','$$$99$$$$$');
F_END_DT/A8 = EDIT('&ENDDATE','$$$$$$9999') || EDIT('&ENDDATE','99$$$$$$$$') || EDIT('&ENDDATE','$$$99$$$$$');
F_PROCEEDS/D16.2 = PROCEEDS;
PAY/YYMD = DATEADD(&STARTDATE,'BD',1);
-* DATEINT/I8YYMD = DATECVT(&STARTDATE, 'YYMD','I8YYMD');
-*PAY/I8YYMD ='&STARTDATE';
-*COMPUTE PAY/I8YYMD = TO_DATE('&STARTDATE','MM/DD/YYYY');

END

i have used SET BUSDAYS='_MTWTF_'
BUT when i run my code the PAY gets displayed as 1901/01/01
i want it to be next day of the &startdate what user enters ex:if &startdate = 11/30/2006 PAY should be displayed as 12/01/2006
problem with date formats
 
Report This Post
Expert
posted Hide Post
Umar,

FIrstly you have to understand how dates are used within WebFOCUS and the various types, for instance 30/11/2006 as I8DMYY will be 30112006 whereas if it is YYMD format then it will be 38685.

Also remember that most (if not all) date functions return a zero when an invalid date is passed.

The base date within WebFOCUS is 31/12/1900 and this is because 1900 was NOT a leap year and it is easier to use a date after February 1900 than deal with it as a non leap year (just ask Microshaft why 60 in date format gives 29/02/1900!!).

So, as a YYMD format, the base date would appear as 0 and adding one day to that would give you 1901/01/01.

If you comprehend this then you will realise that your input into DATEADD function is in the wrong format.

To give you a starter try this DM code -
-SET &STARTDATE = '30/11/2006';
-SET &STARTYYMD = DATECVT(EDIT('&STARTDATE.EVAL','99$99$9999'), 'A8DMYY', 'YYMD');
-SET &STARTPAY  = DATECVT(DATEADD(&STARTYYMD,'BD',1), 'YYMD', 'I8YYMD');
-? &START

Note that it uses DATECVT to get the date into the correct format for the DATEADD function which is YYMD and not A8YYMD as you are attempting.

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
Virtuoso
posted Hide Post
We have some of these settings in our EDASPROF.PRF file so we can use them always

-* this settings are in my EDASPROFso we have them in all our reports available

-SET &DOWK = DOWK(&YYMD,'A4');
-SET &DIFF = IF &DOWK EQ 'MON' THEN -3 ELSE
-                   IF &DOWK EQ 'SUN' THEN -2 ELSE -1;
-* this can be corrected for the holiday calander
-* calculates the previous working day
-SET &&VRG_WRKDAG = AYMD(&YYMD, &DIFF, 'I8YYMD'); 
-*
-* calculate the actual year
-* and the prvious two years
-SET &&LPD_JAAR   = EDIT('&&VRG_WRKDAG.EVAL','9999'); 
-SET &&VRG_JAAR   = &&LPD_JAAR.EVAL - 1;
-SET &&VRG_JR2    = &&LPD_JAAR.EVAL - 2;
-*
-* the actual month based on the preious working day as 'YYYYMM'
-* and the act month
-SET &&LPD_JRMND  = EDIT('&&VRG_WRKDAG.EVAL','999999');
-SET &LPD_MND    = EDIT('&&VRG_WRKDAG.EVAL','$$$$99');
-*
-* the previous month and year as 'YYYYMMD'
-SET &VRG_MNDNUM = IF &LPD_MND.EVAL EQ 1 THEN  12 ELSE &LPD_MND.EVAL - 1;
-SET &VRG_MND    = IF &VRG_MNDNUM.EVAL LT 10 THEN '0'||'&VRG_MNDNUM.EVAL' ELSE '&VRG_MNDNUM.EVAL';
-SET &&VRG_JRMND  = IF &LPD_MND.EVAL EQ 1 THEN '&&VRG_JAAR.EVAL'||'1201' ELSE '&&LPD_JAAR.EVAL'||'&&VRG_MND.EVAL'||'01';
-* 
-* first day of the actual month and the actual year
-SET &&LPD_JMD    = '&&LPD_JRMND.EVAL'||'01';
-SET &&NIEUWJAAR  = '&&LPD_JAAR.EVAL' || '0101';
-* and convert the first day of the year to a number
-SET &&DAGNUMNJ   = DATECVT(&&NIEUWJAAR, 'I8YYMD', 'I8');
-*
-* the same with the previous year
-SET &&VRG_JMD    = '&&VRG_JAAR.EVAL'||'0101';
-SET &&DAGNUMVJ   = DATECVT(&&VRG_JMD, 'I8YYMD', 'I8');
-*
-* convert the last working day to a number
-SET &&DAGNUM     = DATECVT(&&VRG_WRKDAG, 'I8YYMD', 'I8');
-*




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
Master
posted Hide Post
Umar,

Your code is wrong. First, DATEADD requires that the date be in date format. An & variable is not date format, it is just a string of characters. See below and don't be shy about using more than one function at a time.
Define File
  F_START_DT/A8 = EDIT('&STARTDATE','$$$$$$9999') || EDIT('&STARTDATE','99$99$$$$$');
  F_END_DT/A8 = EDIT('&ENDDATE','$$$$$$9999') || EDIT('&ENDDATE','99$99$$$$$');
  F_PROCEEDS/D16.2 = PROCEEDS;
  PAY/YYMD = DATEADD(DATECVT(F_START_DT,'A8YYMD,'YYMD'),'BD',1);
END


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
<Umar Farook S>
posted
Hi
I used and tested the below code but still the output of STARTPAY is 1901/01/01 only.

STARTYYMD/YYMD = DATECVT(EDIT('&STARTDATE.EVAL','99$99$9999'), 'A8DMYY', 'YYMD');
STARTPAY/I8YYMD = DATECVT(DATEADD(STARTYYMD,'BD',1), 'YYMD', 'I8YYMD');

I have assigned -DEFAULT &STARTDATE ='11/30/2006';

please let me know if anything i have done wrong here.
 
Report This Post
<Umar Farook S>
posted
Hi jgelona

i used it and it worked perfectly but i need it to be used along with a condition like

case
when trade_type = 'variable 1' then i have to add one date(here our code or result of our code comes)
else startdate end as paymentdate
 
Report This Post
Master
posted Hide Post
Is this what you mean?
DEFINE FILE 
  F_START_DT/YYMD = DATECVT(EDIT('&STARTDATE','99$99$9999'),'A8DMYY,'YYMD');
  F_END_DT/YYMD = DATECVT(EDIT('&ENDDATE','99$99$9999'),'A8DMYY','YYMD');
  F_PROCEEDS/D16.2 = PROCEEDS;
  PAY/YYMD = IF condition_1
    THEN DATEADD(F_START_DT,'BD',1)
    ELSE F_START_DT;
END


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
<Umar Farook S>
posted
I used the same as given below and i got output of PAY as 1901/01/01 only .

F_START_DT1/YYMD = DATECVT(EDIT('&STARTDATE','99$99$9999'),'A8DMYY','YYMD');
PAY/YYMD = IF TRADE_TYPE = 'Acquisition'
THEN DATEADD(F_START_DT1,'BD',1)
ELSE CLOSING_DATE;
 
Report This Post
Expert
posted Hide Post
Umar,

Go and read the tech manual sections that deal with dates and then, perhaps, you might comprehend that a date of format of DMYY is not the same as a date of format MDYY!!

Hint: The D stands for Day.

Then perhaps you might understand my comment
quote:
Also remember that most (if not all) date functions return a zero when an invalid date is passed.

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
<Umar Farook S>
posted
Hi

WHERE ENTRY_DATE EQ TO_DATE('&ASOFDATE','MM/DD/YYYY')
this condtition is not getting checked properly.
I need to get only the data for matching asofdates and entry dates from the database.

i have declared ASOFDATE as
-DEFAULT &ASOFDATE = '12/01/2006';
 
Report This Post
Virtuoso
posted Hide Post
Umar

is this a new issue?




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
<Umar Farook S>
posted
yes frank
 
Report This Post
Virtuoso
posted Hide Post
please post it as a new question, this is a bit confusing.




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
Expert
posted Hide Post
Frank,

It's not a new issue but another display of Umar's inability to differentiate between date formats!!!

Umar,

12/01/2006 is in the format DD/MM/YYYY

You test is trying to use the different format of MM/DD/YYYY.

What do you not understand?

Apples are not the same as pears.

We will help and assist as much as we can BUT it requires of you to actually do some research and reading of manuals to obtain a basic understanding yourself .... otherwise we ought to be looking for payment for tuition!! Wink

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
<Umar Farook S>
posted
NO 12/01/2006 represents MM/DD/YYYY format only
I like to know how you say that its otherway round.
 
Report This Post
Virtuoso
posted Hide Post
quote:
NO 12/01/2006 represents MM/DD/YYYY format only
I like to know how you say that its otherway round.

Umar,

12/01/2006 is a mm/dd/yyyy format of a smart date.

2006/12/01 is a yyyy/mm/dd format of a smart date.

Of course if you were in Germany they look at dates in different form for placement of where the day is. And then there are military dates in the US which are 01 Dec 2006

So how does India use dates?

Patience and understanding guys. I even have fun interpreting IB's documentation at times.

Have a great week all.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
Umar,

You are, indeed, correct. 12/01/2006 can also be MM/DD/YYYY. I assumed you meant 12th January after your posting above being in the wrong format -
quote:
STARTYYMD/YYMD = DATECVT(EDIT('&STARTDATE.EVAL','99$99$9999'), 'A8DMYY', 'YYMD');
STARTPAY/I8YYMD = DATECVT(DATEADD(STARTYYMD,'BD',1), 'YYMD', 'I8YYMD');

I have assigned -DEFAULT &STARTDATE ='11/30/2006';


Perhaps you have learned?

As for your outstanding problem, what format is your database date ENTRY_DATE?

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
<Umar Farook S>
posted
Hey i got it right
Thanks
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders