[SOLVED] transform an I5 integer date into an a8yymd date
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)
September 05, 2017, 10:38 AM
MartinY
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
September 05, 2017, 01:21 PM
Hallway
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:
September 05, 2017, 02:41 PM
Al_C
-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)
September 05, 2017, 03:45 PM
Francis Mariani
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
September 05, 2017, 03:49 PM
Francis Mariani
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
September 06, 2017, 06:47 AM
Al_C
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)
September 06, 2017, 09:53 AM
Francis Mariani
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
September 06, 2017, 04:03 PM
Hallway
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.
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:
September 06, 2017, 04:37 PM
Hallway
To expand a little more, consider the following code:
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: