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.
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) ;
I was hoping I could eliminate the need to concatenate the dummy time value and simplify the date calculation, for example:
), ISO ) as GLDATEISO
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.