July 11

0 comments

Add days to 100 Year date from EXCEL using RPG

By NickLitten

July 11, 2017

date, julian

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.

Many applications (especially mainframe systems) store dates in the Julian format. Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BC (on the Julian calendar). Almost 2.5 million days have transpired since this date. Julian dates are widely used as time variables within astronomical software…

Julian Dates in IBM i are different

Julian Dates in IBMi (and the earlier AS400 and iSeries) machines are stored showing the YY/DDD

Format nameDate-format parameterDate format and separatorField lengthExample
Job default*JOB   
Month/Day/Year*MDYmm/dd/yy806/21/90
Day/Month/Year*DMYdd/mm/yy821/06/90
Year/Month/Day*YMDyy/mm/dd890/06/21
Julian*JULyy/ddd690/172
International Standards Organization*ISOyyyy-mm-dd101990-06-21
IBM® USA Standard*USAmm/dd/yyyy1006/21/1990
IBM European Standard*EURdd.mm.yyyy1021.06.1990

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 were utter whackjobs.

Luckily its simple to figure out programmatically using RPG4:

 D  DS
  // 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

  $100YearDate = %date('1899-12-31');

  $resultDate += %Days( $100YearDate );

  Dsply %char($resultdate);

 *inlr = *on;

Interestingly – you cannot use INZ on the ‘D’ spec to initiliase the variable 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 😉

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>