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

Follow

About the Author

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.