add days to 100 Year date from EXCEL using RPG
So, we had a file coming in from an external partner today – containing a date in Julian Format. Or what I initially thought to be Julian format.
[quote=Some Website Out There]Many applications (especially mainframe systems) store dates in the Julian format, which is a 5 digit number, consisting of a 2 digit year and a 3 digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year...[/quote]
Then when I looked at the date in question I realised the value was 42345 but this was representing an actual date of December 8th 2015! So, this clearly isnt a Julian date... Hmmm...
Cutting to the chase, it turns out that this date is stored in what the customer refers to as '100 year date format' which is basically the number of days since the last day of 1899. Which just goes to show that those crazy Victorian's where utter whackjobs.
Luckily its simple to figure out programmatically using RPG4:
// Your weird JULIAN date field is called $100YearDate :)
D $100YearDate 10I00 INZ(42345)
// setup the old date that is even older than john lowrie
D $OldDate 10D DATFMT(*ISO)
// setup date variables for the result
D $ResultDate D DATFMT(*ISO)
// I'm assuming your weird JULIAN date field is called $100YearDate :)
$OldDate = %date('1899-12-31');
$ResultDate = $OldDate + %Days( $100YearDate );
*inlr = *on;
Interestingly - you cannot use INZ on the 'D' spec to initiliase the variable $OldDate to 1899 because the compiler complains that the date is just too old. But it does allow us to put the value in there during mainline calcs.
So, the ResultDate is basically taking the start date (12/31/1899) and adding the number of days we receive from the file.
Not brain surgery but interesting enough for me to want to record what I did.
And it just may help some other SteamPunk RPG Programmer out there ;)