I had to pull records from two files with a matching date value in each file. To make things interesting, the date on one file was stored in timestamp format and the date field in the other file was stored in hundred year date format.
SQL (and QUERY400) can very nicely convert dates stored in internal ‘DATE-FIELD’ 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.
Luckily SQL lets use convert date values on the fly and then allows to compare.
This hotel reservation file and I am joining on the reservation value (‘_RESERVATIONID_’) and because I wanted to see the values – I showed them in the select statement. The files in this case are FILE1 which stored in 100 year date format (‘_HUNDREDYEARDATEFIELD_’), and FILE2 which stores in timestamp format (‘_TIMESTAMPFIELD_’):
SELECT ALL DATE(_HUNDREDYEARDATEFIELD_+DAYS('12/31/1899')) AS SMELLY100DATE, DATE(_TIMESTAMPFIELD_) AS SMOOTHTIMESTAMPDATE, T01.FIELD1, T01.FIELD2, T01.FIELD3, <--more-fields-here FROM FILE1 T01 INNER JOIN FILE2 T02 ON T01._RESERVATIONID_ = T02._RESERVATIONID_ WHERE _RESERVATIONID_ > ' ' <-- select confirmed reservations only AND _HUNDREDYEARDATEFIELD_ BETWEEN 43190 AND 43219 <-- we can select using HYD values AND DATE(NAL_TMESTP) > DATE(_HUNDREDYEARDATEFIELD_+DAYS('12/31/1899')) <-- where the reservation date on file2 is greater than the date on file1
Bloody silly dates….
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.
Encrypt IBM i File (Table) Data with no RPGLE changes using SQL
How to read a Data Area (*DTAARA) using IBM i SQL
IBM i SQL – Using SUBST to insert data into a string
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
How to change MMDDYY to CYYMMDD in one line of RPGLE