IBM i SQL statement to convert or compare hundred year date format

AS400

May 24

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.

Example
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….