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     [SOLVED] transform an I5 integer date into an a8yymd date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] transform an I5 integer date into an a8yymd date
 Login/Join
 
Member
posted
the date is extracted from a string and turned into an integer as follows

-* extract the date substring
D_PABI_EFF_DATE/A5 = GETTOK(HL_DELETED_RECORD,110,9,'&FM',5,D_PABI_EFF_DATE);

-* TURN THE STRING INTO A NUMBER - BUT WE CAN ONLY TURN THE NUMBER INTO FLOATING POINT
D_PABI_EFF_DATE_D/D5.0 = ATODBL(D_PABI_EFF_DATE,'05',D_PABI_EFF_DATE_D);

-* TURN THE FLOATING POINT INTO AN INTEGER
D_PABI_EFF_DATE_I/I5 = D_PABI_EFF_DATE_D;

and here's where I have my problem

the dates all seem to be 67 years too old. is there a way to get the date "base / offset "
into the datatype so it calculates the date correctly without all these gyrations?


this is close, but i was looking for a way to have the system routines do this automagically

DATE_TEST/A8YYMD = DATEADD(18118, 'Y', 67);

going from one date format to another is pretty easy, but since this integer was never defined as a date there seems to be something missing "behind the scenes"

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 7.6.9
Windows
all output (Excel, HTML, PDF)
 
Posts: 28 | Registered: April 27, 2010Report This Post
Virtuoso
posted Hide Post
quote:
HL_DELETED_RECORD

What is the input format of the above date (if it's the date) and where is it coming from (flat file, SQL table, ...) ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
I agree with MartinY. What does the field HL_DELETED_RECORD look like? According to the GETTOK function, that field has a length of 110 characters and you are looking for the 9th token from the left and taking the next 5 characters. Also, What is the value of your variable '&FM' that you are using for the token to look up?


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Member
posted Hide Post
-SET &FM = HEXBYT(253,'A1');
is the token that separates the fields in the string. using the actual character in single quotes didn't work with gettok

when our system deletes records in some tables, the fields are strung together into a single field delimited by chr253.

the gettok gets a 5 character number that should reflect the delete date of the record.
In this example 15955

the deleted record:
0476823ýýVTJTYWx0ZWQT05xDPLoXeV9AgOsntt0HKeM=ÿý241
070417ýýýEýYý15955ýYýCý15955ý0476823ý15957ýBMOON


WebFOCUS 7.6.9
Windows
all output (Excel, HTML, PDF)
 
Posts: 28 | Registered: April 27, 2010Report This Post
Expert
posted Hide Post
Using your brain, how would you translate 15955 to a date? What is the format of this five digit 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
Expert
posted Hide Post
Remember Y2K? If the year is two-digit, there are WebFOCUS date settings to help determine the century.

From the manual:

quote:
A 100-year window that assigns a century value to a two-digit year stored in the field. Two attributes define this window: DEFCENT and YRTHRESH. See Unique FOCUS Topics on the WebFOCUS Documentation CD.
quote:
The DEFCENT parameter defines a default century globally or on a field-level for an application that does not contain an explicit century. DEFCENT is used in conjunction with YRTHRESH to interpret the current century according to the given values. When assigned globally, the time span created by these parameters applies to every 2-digit year used by the application unless you specify file-level or field-level values. (See YRTHRESH.)
quote:
The YRTHRESH parameter defines the start of a 100-year window globally or on a field-level. Used with DEFCENT, interprets the current century according to the given values. Two-digit years greater than or equal to YRTHRESH assume the value of the default century. Two-digit years less than YRTHRESH assume the value of one more than the default century. (See DEFCENT.)


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
quote:
Using your brain, how would you translate 15955 to a date? What is the format of this five digit date?

Thank you,

Francis



snarkiness aside. I would have opened a support call since after reading a good portion of the online help i saw no mention of these settings.
furthermore, I don't have the security to modify master files. I realize it's good to have "the keys to the kingdom", but not all organizations operate that way. Thanks for pointing me in the general direction. That's all I wanted


WebFOCUS 7.6.9
Windows
all output (Excel, HTML, PDF)
 
Posts: 28 | Registered: April 27, 2010Report This Post
Expert
posted Hide Post
I wasn't being snarky. I was trying to ask you how you would translate the integer to a date without using code, so I could try to come up with code to replicate the thought process. Brain = thought?


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
Master
posted Hide Post
I don't think that Francis was being snarky. I understood him as meaning when you personally see the integer 15955, what is the date that you want that integer to translate to?

For example in Excel 15955 translates to 1943/09/06 or an offset of 15,955 days where day 1 = 1900/01/01.

In WebFOCUS, 15955 translates to 1944/09/06 or an offset of 15,955 days since where day 1 = 1901/01/01.

-SET &NEWDATE = EDIT(DATECVT(15955, 'YYMD', 'A8YYMD'), '9999/99/99')
-TYPE &NEWDATE  

So, what is the date that you are thinking that it should be?

If that integer (15955) is an offset like Excel and WebFOCUS uses, and we can figure out what day 1 equals on your system, then we can know how many days to add to the integer to make it the correct date in WebFOCUS.

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Master
posted Hide Post
To expand a little more, consider the following code:
-SET &ECHO = ALL;
-SET &D_PABI_EFF_DATE_I = 15955;
-SET &DAYSTOADD = 24471; 
-SET &NEWDATEOFFSET = &D_PABI_EFF_DATE_I + &DAYSTOADD;
-SET &NEWDATE = EDIT(DATECVT( &NEWDATEOFFSET, 'YYMD', 'A8YYMD'), '9999/99/99');
-TYPE &NEWDATE 

  • I set the variable &D_PABI_EFF_DATE_I to the integer 15955 in the example you showed
  • You mentioned that it seemed about 67 years too old, so I set &DAYSTOADD as 24471 (67 * 365.25 = 24471.75 so I truncated off the decimal)
  • I add the two together and converted it to an alpha date
  • This gives a date of 2011/09/06

So you need to adjust +/- the &DAYSTOADD to make 15955 be the date that you think it should be.


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Master
posted Hide Post
quote:
this is close, but i was looking for a way to have the system routines do this automagically

DATE_TEST/A8YYMD = DATEADD(18118, 'Y', 67);


Using the example above, if you enter in the code
-SET &DATE_TEST = DATEADD(1, 'Y', 67); 
-TYPE &DATE_TEST 
You get 24472, and that date in WebFOCUS is 1968/01/01. So it looks like your system offsets where day 1 = 1968/01/01. So you will probably just need to add 24472 to the integer you get.

I'm not sure what you want your final output to be, but you should be able to do the following to get a formatted alpha date with forward slashes with just one line of code:
D_PABI_EFF_DATE/A10 = EDIT( DATECVT( EDIT( GETTOK(HL_DELETED_RECORD,110,9,'&FM',5,D_PABI_EFF_DATE) ) + 24472, 'YYMD', 'A8YYMD'), '9999/99/99')  ;  


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report 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     [SOLVED] transform an I5 integer date into an a8yymd date

Copyright © 1996-2020 Information Builders