How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats


May 24

Back in the good old days of single megabyte disks the size of a shoebox, when hard drives were called Winchester Drives and the only phones we used were actually used for making telephone calls, size was everything (insert slightly rude joke here). In the daily effort to shave off a byte here and a nibble there — a nibble is half a byte 😉 — Database field sizes were considered an easy candidate for saving a teeny bit of disk. So, date-field’s were commonly stored in strange smaller formats to reduce space by a single character. This has lead to a plethora of different date formats in various IBM i application databases.

Over the years I’ve seen many AS400 and iSeries horribly designed application databases, flaunting the rules of database normalisation, wasting space with humongous fixed length fields, adding unused spare fields… but then the dbase designer has decided to complicate date field storage to save a single byte. Nowt Strange as Folk.

Whats in a Date?

June 25th 1967 might be stored as

  • DDMMYY – 250667 – Which is fine for people in England, but hard to sort into a nice sequence for reporting. It’s confusing for Americans who think in MMDDYY
  • YYMMDD – 670625 – This was the most common date format back in the early days of computing. It allows for nice sorting of dates in ascending and descending format… until you hit the year 2000… #Y2K was born so we added
  • CYYMMDD – 0670625 – Adding an extra byte to the front of the date “the century indicator” gets around the problem of dates flowing into the new millennium. Jan 1st 200 was stored as 1000101
  • HYD – 24648 – This Hundred Year Date (sometimes called the Excel Date) is the number of days since ’12/31/1899′ until the date in question. So Jan 1st 1900 was ‘1’ and June 25th 1967 is 24,648. It’s stored as a 5 digit numeric. Mental right?

Luckily, times have improved and nowadays, limited disk storage is not the same problem it was decades ago.

We generally store dates in larger, clearer formats nowadays:

  • DATE – 19670625 – This full form YYYYMMDD is commonly stored in modern systems and very easy to maintain.
  • TIMESTAMP – 19670625114500 – The timestamp simply takes the DATE value and appends a time to it. This is another very common date format stored in modern IBM i Systems.

The IBM i operating system is a wonderful beast – and it handles all the old AS400 and iSeries application databases with ease: Being able to run with these old databases, without missing a beat, is a great bonus for business but can be a pain for software developers – we frequently have to interface between old legacy databases and more modern ones.

How do we compare different date formats?

SQL (and QUERY400) can very nicely convert dates stored in internal ‘TIMESTAMP’ format to MM/DD/YY very easily using the DATE(fieldname) function.

SQL (and QUERY400) can very nicely convert dates stored in internal ‘ONE HUNDRED YEAR DATE’ format to MM/DD/YY very easily using the DATE(fieldname+DAYS(’12/31/1899′)) function.

Let’s imagine we have a field called CUSMASTER which has a Hundred Year Date Field called SMELLY100DATE, and another called DODGYTIMESTAMPDATE and we want to see any dates greater than January 24th 2014.

We could just say:

select * from CUSMASTER where date(SMELLY100DATE+days('12/31/1899')) > 140124

Similarly we could check the same thing if the field was a TIMESTAMP

select * from CUSMASTER where date(DODGYTIMESTAMPDATE) > 140124

Pretty easy huh?

Obviously we can also compare dates against two different files – where one file is using HYD and another uses TIMESTAMP format.

This morning I had this exact situation and created a QUERY to this comparison. And then uplifted that query into SQL so we could easily run it from the IBM i SQL Explorer.