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.
June 25th 1967 might be stored as
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:
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.
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.
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
Modernize AS400 iSeries Queries – Convert to IBM i SQL
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