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