.st0{fill:#FFFFFF;}

Add days to 100 Year date from EXCEL using RPG 

 July 11, 2017

By  NickLitten

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.

snug cbd discount coupon 15% off

20% Off with Coupon: NICKLITTEN

I highly recommend the SNUG CBD Tincture to help keep you in the zone when programming!

In Partnership with SNUG CBD - American readers get 20% off

And it just may help some other SteamPunk RPG Programmer out there 😉

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

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

Get In Touch

I’m always looking for awesome input, feedback and critique!

>