TIMESTAMP_FORMAT

[Stephen Landess <steve_landess@xxxxxxxxxxx>]

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