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.

[quote=Some Website Out There]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…[/quote]

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 name Date-format parameter Date format and separator Field length Example
Job default *JOB
Month/Day/Year *MDY mm/dd/yy 8 06/21/90
Day/Month/Year *DMY dd/mm/yy 8 21/06/90
Year/Month/Day *YMD yy/mm/dd 8 90/06/21
Julian *JUL yy/ddd 6 90/172
International Standards Organization *ISO yyyy-mm-dd 10 1990-06-21
IBMĀ® USA Standard *USA mm/dd/yyyy 10 06/21/1990
IBM European Standard *EUR dd.mm.yyyy 10 21.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 ;
 *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 šŸ˜‰


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"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

Iā€™m always looking for awesome input, feedback and critique!