Error when extracting YYMMDD dates from table in AS/400

Question

I have an AS/400 database to which I connect using the 5250 or DBeaver, when it was created more than 40 years ago, a field called BIRTHDT was created with the format YYMMDD meaning when I run the query

SELECT name, 
       birthdt,
       varchar_format(timestamp_format(birthdt,'YYMMDD'),'DD/MM/YYYY') AS Birthdate 
FROM QS36F.Table

I get for 840212 the value 12/02/2084, so this person will be born in 2084, any way to fix it?

Answer 1

One way to resolve this issue is by adding a check on the birth date to determine if it falls within the 19th or 20th century. For example, any date with the year after '24' should be considered as part of the 19th century :

SELECT BIRTHDT,
       varchar_format(
         timestamp_format(
              CASE WHEN birthdt > 240000 THEN '19' || birthdt ELSE '20' || birthdt END ,
              'YYYYMMDD'),
         'DD/MM/YYYY'
       ) AS Birthdate 
FROM mytable;

For this dataset :

CREATE TABLE mytable (
  birthdt INTEGER
);

INSERT INTO mytable VALUES
('840212'),
('150212');

Results :

BIRTHDT BIRTHDATE
840212  12/02/1984
150212  12/02/2015

Demo here