Page 1 of 1
Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 5:23 pm
by bbunney
I have a 5 digit field that contains a 100 year date (Number of days difference between the current date and 1899-12-31) and a 5 digit time field (HHMMSS) and I need to convert them to an *ISO timestamp. They are in a a very old file. How would I do that? Thanks.
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 6:08 pm
by jonboy49
If it is a 5 digit time field it cannot contain an HHMMSS value unless there is also an AM/PM indicator. Can you clarify please.
The basic calc would be something like:
Code: Select all
**free
dcl-s realDate date;
dcl-s numDate int(10) inz(1); // 1 day past the base
dcl-s numTime int(10) inz(1); // And first minute of that day
dcl-s realTimestamp timestamp;
dcl-c BASEDATE D'1899-12-31';
realDate = BASEDATE + %Days(numDate);
dsply %Char(realDate);
realTimestamp = realDate + %Time(numTime);
dsply %char(realTimestamp);
*InLr = *On;
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 6:23 pm
by bbunney
It does not contain HHMMSS. It's just a number. Here is what the 2 fields values are:
Code: Select all
100 YR TRANS DATE TRANS. TIME
42,109 170,931
42,109 171,058
42,142 111,027
42,254 31,357
42,254 35,251
42,254 40,432
42,254 40,915
42,444 75,103
42,444 80,958
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 6:29 pm
by Scott Klement
From the first post in the thread:
bbunney wrote: ↑Mon Mar 25, 2024 5:23 pm
... and a 5 digit time field (HHMMSS) ...
From the most recent post:
bbunney wrote: ↑Mon Mar 25, 2024 6:23 pm
It does not contain HHMMSS. It's just a number.
. . .
Code: Select all
100 YR TRANS DATE TRANS. TIME
42,109 170,931
42,109 171,058
. . .
I'm very confused. You have told us both that it does and doesn't contain an HHMMSS value, and you've told us that it is 5 digits, but are showing 6 digit numbers in it.
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 6:54 pm
by bbunney
The hundred year date field is 5 digits (42109) and the time field is 6 digits (170931).
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 7:26 pm
by jonboy49
Then the code I gave you should work. Just substitute your own variable names for the numDate and numTime.
IF - the number represents HHMMSS. If it doesn't then we need to know what it does contain!
Re: Converting Hundred Year Date Plus Time to Timestamp
Posted: Mon Mar 25, 2024 7:43 pm
by bbunney
Agreed. Thank you so much jonboy.