TIMESTAMP_FORMAT

As some of you know, JDE stores dates as a six-digit number in the form CYYDDD, with C=0 for dates earlier than year 2000, and C=1 for dates >= 2000.

Examples:
099001 is Jan 1, 1999.
120001 is Jan 1, 2000.

I have various methods used to convert these values to a date data type, and it seems that the SQL scalar function TIMESTAMP_FORMAT (or TO_DATE, which performs the same function) would be a good way to do it.

However, it seems that I must create a dummy field with a time value and concatenate the date and time values to get TIMESTAMP_FORMAT to work properly

See example below, where GLTM is the dummy field ( and adding 1900000 to the GLDT date value is necessary to get the date values into YYYYDDD format needed for TIMESTAMP_FORMAT)

Drop table qtemp.datetest;

CREATE TABLE QTEMP.DATETEST
( GLDT NUMERIC (6 , 0) NOT NULL WITH DEFAULT
, GLTM NUMERIC (6 , 0) NOT NULL WITH DEFAULT) ;

insert into qtemp.datetest
values (121001,120000)
, (121002,120000)
, (121002,120000)
, (121002,120000) ;

select
gldt
, gltm
, CHAR(
DATE(
TIMESTAMP_FORMAT( (1900000+GLDT) CONCAT DIGITS(GLTM)
,'YYYYDDD HH24MISS'
)
), ISO ) as GLDATEISO
from qtemp.datetest ;

Running the above script in Run SQL Scripts produces:

GLDT GLTM GLDATEISO
121001 120000 2021-01-01
121002 120000 2021-01-02
121002 120000 2021-01-02
121002 120000 2021-01-02

I was hoping I could eliminate the need to concatenate the dummy time value and simplify the date calculation, for example:

select
CHAR(
DATE(
TIMESTAMP_FORMAT( (1900000+GLDT)
,'YYYYDDD'
)
), ISO ) as GLDATEISO
from qtemp.datetest;

This SQL statement fails with :

SQL State: 42815
Vendor Code: -171
Message: [SQL0171] Argument 1 of function TIMESTAMP_FORMAT not valid. Cause . . . . . : The data type, length, or value of argument 1 of function TIMESTAMP_FORMAT specified is not valid. Recovery . . . : Refer to the DB2 for IBM i SQL Reference topic collection in the Database category in the IBM i Information Center for more information on scalar functions. Correct the arguments specified for the function. Try the request again.

I have not yet found a definitive list of the date format strings used by these two functions.

The question is:

Is there a format string for TIMESTAMP_FORMAT (and TO_DATE) which allows conversion of YYYYDDD date values without having to concatenate a time value

??
Steve Landess
(512) 289-0387