What you want? To convert a numeric date in the format CYYMMDD into a real
date?
IMHO the easiest way is to use the following formula:
Date(Right(Digits(YourDate + 19000000), 8) concat '000000')
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Stephen Landess
Sent: Mittwoch, 10. November 2021 17:02
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: 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
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
