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 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|
|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:
// 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 );
*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, Alpha Nerd 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 in the words of the most interesting man in the world: Stay thirsty my friend.
IBM i SQL statement to convert or compare hundred year date format
How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats
Use RPG to find the day of the week
Add days to 100 Year date from EXCEL using RPG
Convert RPG Datefield %DATE into a signed numeric
another as400 jba date conversion routine