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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     losing my mind with dates

Read-Only Read-Only Topic
Go
Search
Notify
Tools
losing my mind with dates
 Login/Join
 
Member
posted
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
 
Posts: 10 | Registered: November 21, 2007Report This Post
Virtuoso
posted Hide Post
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;


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
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
 
Posts: 10 | Registered: November 21, 2007Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
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???
 
Posts: 10 | Registered: November 21, 2007Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
i caught the YYMD and made the change no worries, i am using focus 7.38 in mainframe, not dev studio
 
Posts: 10 | Registered: November 21, 2007Report This Post
Member
posted Hide Post
the dateassign field shows up in format A8 under the
?FF results
 
Posts: 10 | Registered: November 21, 2007Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Member
posted Hide Post
thanks Leah , i have a tough time relating that to my own but we'll see how it goes
 
Posts: 10 | Registered: November 21, 2007Report This Post
Member
posted Hide Post
i think this might be over my head right now, im completely lost
 
Posts: 10 | Registered: November 21, 2007Report This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Guru
posted Hide Post
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


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Virtuoso
posted Hide Post
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.




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
Guru
posted Hide Post
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


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Guru
posted Hide Post
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.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Platinum Member
posted Hide Post
ok simple enough solution. 1. legacy date? use datecvt, and then aymd user written subroutine.
if smart date then simply add 30.

Ira - SONJ


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
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?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 10 | Registered: November 21, 2007Report This Post
Expert
posted Hide Post
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.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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)
 
Posts: 10 | Registered: November 21, 2007Report This Post
Member
posted Hide Post
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 Smiler
John
 
Posts: 10 | Registered: November 21, 2007Report This Post
Expert
posted Hide Post
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?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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,


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
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.




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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     losing my mind with dates

Copyright © 1996-2020 Information Builders