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

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;

Hi Leah
can you please give me a little explanation how your code works.

Please also let me know about the formats of your date &LETDATE

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.

Hi Leah,
i will try this and let you know

What does LETTDATM do?

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

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?

In this instance, we're just calculating the date to print on a report, so we make it 'pretty'.

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 .

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

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

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');
-*

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

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.

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

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

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;

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

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';

Umar

is this a new issue?

yes frank

please post it as a new question, this is a bit confusing.

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!!

T

NO 12/01/2006 represents MM/DD/YYYY format only
I like to know how you say that its otherway round.

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.

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

Hey i got it right
Thanks