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,

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, ...) ?

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?

-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

Using your brain, how would you translate 15955 to a date? What is the format of this five digit date?

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.)

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

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?

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,

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.

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