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?
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
I think you'll find there's a lot more issues to deal with trying to convert a 6-digit numeric value into a actual date value. Stuff like 230229, 000000, 999999, or even all blanks if the data is in a character field.
Rather than handling all those cases directly in your select statement, you should build a user defined function (UDF) that will handle the date conversions for you.
Or you can download and use Alan Campin's iDate Date conversion functions.
Then your select becomes
SELECT BIRTHDT, iDate(BIRTHDT, '*YMD') as Birthdate
FROM mytable;
Note I've dropped the VARCHAR_FORMAT. It's really not needed once the data is in an actual date data type. Simply set the default date format to *USA for whatever query tool you are using. That will give you MM/DD/YYYY output for dates.
