Not the actual answer to your question, but install idate and forget about it. It just works.
https://www.think400.dk/downloads.htm
On Wed, 2021-11-10 at 16:01 +0000, Stephen Landess wrote:
[You don't often get email from steve_landess@xxxxxxxxxxx<mailto:steve_landess@xxxxxxxxxxx>. Learn why this is important at
http://aka.ms/LearnAboutSenderIdentification.]
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
[
https://www.medtronsoftware.com/img/MedtronMinilogo.bmp]
Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550<tel:985-893-2550>
Toll Free: 877-893-2550<tel:877-893-2550>
https://www.medtronsoftware.com
CONFIDENTIALITY NOTICE
This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.
midrange.com
