I mentioned in last week's post Change date from one format to another using SQL that when I converted the alphanumeric value '022195' using SQL's timestamp_format function with the format 'MMDDYY' the result was the date '02212095', which was not what I expected.
I want to thank Chris Ringer, Birgitta Hauser, and R Flagler for the solution.
By using 'YY' I stated that the year was in the current century, therefore '95' was converted to '2095'.
If I wanted to use the standard IBM date "windowing" I should use 'RR'. This would give me '1995', which is what I wanted.
Using the same data as I did in the previous post if I change my SQL statement to:
select char(date(timestamp_format(alpha,'MMDDRR')),iso) as converted_date from testfile
Then the output would be as follows:
This article was written for IBM i 7.2, and it should work with earlier releases too.