all i want is to be bale to add 30 dates from my "dateassign" field, and have that input show in the "new assign" field, i am using this, and this takes the dateassign as "today" and dds the 30 days, not the actual dateassign, i know its obvious what im doing wrong (in bold) but ive been looking at this way too long... heres what Im running:

DEFINE FILE ABCMSA
DATEASSIGN/YYMD='&DATEYYMD';
NEWASSIGN/MDYY=DATEASSIGN+30;
END

SET COUNTWIDTH =ON
TABLE FILE ABCMSA
SUM ACCTBAL/D12.2M AS 'TOTAL OUSTANDINGS'
CNT.ACCTBAL AS '# OF ACCOUNTS'
PCT.ACCTBAL/P8% AS '% OF POOL'
AVE.ACCTBAL/D12.2M AS 'AVERAGE BALANCE'
BY ASTATUS
BY DATEASSIGN
BY NEWASSIGN
IF LPD LT '20071102'
IF ASTATUS EQ '123'
IF RECOVCD EQ 'Grt1'
ON ASTATUS SUB-TOTAL
END

Have you just tried, &YYMD, also, you may or may not have issues with the data formats. Dates and I don't always get along either.

I usually do

  
DATEX/YYMD = '&YMDD';
NEDATE/YYMD = DATEX + 30;

the problem is that the "dateassign" dates are all different dates from the past, this report just gives me 11/30/07 as the dateassign and adds 30 days to that, i need it to add 30 days to all dates in there this is the output:

ACCTSTATUS DATEASSIGN NEWASSIGN TOTAL OUSTANDINGS # OF ACCOUNTS % OF POOL AVERAGE BALANCE
---------- ---------- --------- ----------------- ------------- --------- ---------------
123 2007/11/30 2007/12/30 $77755.77 7501 100% $10.36

*TOTAL ACCTSTATUS 412 $77755.77 7501 100% $10.36

If the date you want to change is a 'smart date' all you have to do is add 30 to it. What format in your database is the 'old date' stored as?

Oh and as an after thought, since you are new, adding the versions you are using to your profile is most helpful.

i imagine it to look like
Dateassign New assign
2007/10/02 2007/11/02
2007/10/14 2007/11/14
but thats not how its outputting, as you can tell i am relativly new to focus and learning more each day
by format you mean YMDD etc???

If you are using developer studio to access the master file, it should show you the format of the date. What database is your information stored in.

YYMD should have typed not YMDD, sorry for that one. Surprised I haven't heard from others.

There is online help for date formats as well. Managed Reporting or Developer Studio, so knowing what you are using would help.

I've been at it for years and still learn something new almost every day.

i caught the YYMD and made the change no worries, i am using focus 7.38 in mainframe, not dev studio

the dateassign field shows up in format A8 under the
?FF results

quote:

Originally posted by Johnny Jones:
i caught the YYMD and made the change no worries, i am using focus 7.38 in mainframe, not dev studio

Ah good old mainframe days. Well you might want to look at the 'focmstr' file if you can and whatever it is called in your shop, for the MFD's. If it is A8 then you'll have to convert it to a smart date, then add the 30 and convert back to an alpha date.

Here's an example from a very old program I have from my mainframe days where I had to convert a date that was A6 I've changed to show for A8

  CAND_DT/A8 extracted from a string
CAND/I8MDYy = EDIT(CAND_DT)
CANDM/MDYy = CAND;         
CANDY/YYMD = CANDM;     

Add your 30 then reverse the process.

thanks Leah , i have a tough time relating that to my own but we'll see how it goes

i think this might be over my head right now, im completely lost

see if this helps

DEFINE FILE CAR
DATE1/A8 = '11/30/07';
MM1/A2 = EDIT(DATE1,'99$$$$$$');
DD1/A2 = EDIT(DATE1,'$$$99$$$');
YY1/A2 = EDIT(DATE1,'$$$$$$99');
CENTURY/A2 = IF YY1 GE '90' THEN '19' ELSE '20';
YEAR2/A4 = CENTURY || YY1;
YEAR3/A8MDYY = MM1 | DD1 | YEAR2;
NEW_DATE/MDYY = YEAR3;
ADD_DATE/MDYY = NEW_DATE + 25;
END
TABLE FILE CAR
PRINT MODEL NEW_DATE ADD_DATE
BY COUNTRY
END

quote:

CAND_DT/A8

Think of CAND_DT as the date in your database that has the A8 designation. Of course Spence's approach would work, is the date stored with the '/' or does is display with them? You never did say what the data base source was.

Try this one


DEFINE FILE EMPLOYEE
DATEASSIGN/A8 = '20070101';
DATEASSIGN_I8YYMD/I8YYMD = EDIT(DATEASSIGN);
NEWASSIGN/I8YYMD = AYMD(DATEASSIGN_I8YYMD, 30, 'I8YYMD');
DAT_INC_YYMD/I8YYMD = DATECVT(DAT_INC, 'I6YMD', 'I8YYMD');
NEXT_INC_DATE/I8YYMD = AYMD(DAT_INC_YYMD,30, 'I8YYMD');
END
-*
TABLE FILE EMPLOYEE
PRINT
EMP_ID
LAST_NAME
FIRST_NAME
DATEASSIGN
NEWASSIGN
DAT_INC
NEXT_INC_DATE
END

John

Dates are complex.
Thats why there is a very good book available for only 25 U$ and it's worth the money.

You need to know a bit more.
Smartdates?
legacy dates??

In the old (legacy) databases your dates are in fact a string like '20070501' or a number that looks like a date (19870705).
If you add just 30 to the last one the result would be 19870735...and that's not a date.

In the "modern" databases your dates will have formats like YYMD or MDYY or YYMDh. And what you can do with it depends on that format.

WF has a lot of formulas to handle this all, and a lot of "older" user have developed all kind of tricks to handle this.

The book will help you.

Here on the forum you also can find a lot of examples that might help you a bit.

Glenda's solution is based on a legacy date format.

John,

The book Frank was referencing has been a life saver to me. I would gladly have paid more for it.

The book is called (Almost) 1001 Ways to Work With DATES in WebFOCUS. You should be able to find it on IBI.

Take Frank's advice and order one.


Take

Hi,

At your place, I would first understand:

1/ The type of my original date field: DateTime or only date.
2/ The date type of the field I am expecting
3/ Depending on these 2 points use some existing functions that will add the number of days to the existing date.

Majid.

ok simple enough solution. 1. legacy date? use datecvt, and then aymd user written subroutine.
if smart date then simply add 30.

Ira - SONJ

Johhny Jones, don't despair, this is easy-peasy.

You have a date field in a table defined as A8? You simply need to convert it to a Date field and then add 30 to it. In the example below - it takes defining two virtual fields to achieve this:

ADATE is your database table field defined as A8 - you would not be defining this field in your program.
BDATE is the alpha date field converted to a Date field using the DATECVT function.
CDATE is BDATE plus 30 days.

It's as simple as that.

DEFINE FILE CAR
ADATE/A8 = '&YYMD';
BDATE/YYMD = DATECVT(ADATE, 'A8YYMD', 'YYMD');
CDATE/YYMD = BDATE + 30;
END
TABLE FILE CAR
PRINT
ADATE
BDATE
CDATE
BY CAR
END

To kick things up a notch, are you sure you want to add 30 days? Or do you want to add 1 month?

If you want to add 1 month, you can define the virtual field like this by using the DATEADD funtion:

CDATE/YYMD = DATEADD(BDATE, 'M', +1);

There's no mystery to this, you just have to look at it logically and Know Your Date Functions.

Meanwhile, we could go into a lot of other frustrations with dates, but let's leave that for another day, eh?

ALL
many many thanks for all the input, I will definately go ahead and pick that book up sounds like the holy grail of dealing with dates,
in the end up the only one that I could get to work in any way was the input given by Francis

but..... my 'Adate' can have historical dates, this entry makes it show as todays date, which is not correct.... ugh ugh ugh
John

Like I said, DO NOT code the ADATE line. It represents your database alpha date field, which should contain different dates, it should not contain today's date.

whooops my fault, this finally worked...this has been a great learning experience, again thanks to all who took the time to literally walk me through this...
John (total newbie)

and now.. for my next challenge, Im going to try and state instead of showing a date 30 days out, im going to try and give it a countdown # for example, datea= 11/05/07 new date 12/06/06 if i ran it today it would show "3" (as its 3 days until it reaches the 30 day mark... ahh nothing like getting in over my head
John

Johnny, please confirm that the dates you posted in your query regarding "countdown" are correct:

11/05/07 and 12/06/06? How do these dates have a countdown of 3?

Subtract &YYMD from newdate + 1 to get the countdown. In Francis' example it would be

CURRDATE/YYMD=&YYMD;
COUNTDOWN/D=CDATE - CURRDATE+ 1;

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

I think Johnny made a error in typing.

12/06/06 should be 12/06/07 and in text this is 6 december 2007

It might be easier to understand if we should know what you want, and what the database is used for.

For example: a loan database with a loan start date of 5 Nov 2007 and a term of 30 days.
Several days before expiration you want to see the loans that will expire.

If you explain it that way, it would be easier to think with you on solutions.