Re: TIMESTAMP_FORMAT -- MIDRANGE-L

Kevin wrote:

Not the actual answer to your question, but install idate and forget about it. It just works.

Kevin -

Good idea! I had forgotten about iDate. Done!

I think I gave Alan some input on JDE date values to enhance the iDate functionality, but I've had a heart attack and dain bramage since then.

However, I still want to know if TIMESTAMP_FORMAT can do it without requiring the time component...

Regards
Steve Landess
(512) 289-0387

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>
Sent: Wednesday, November 10, 2021 10:07 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: TIMESTAMP_FORMAT

https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.think400.dk%2Fdownloads.htm&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ucJYCBwoJwqzR86JxyFQTkysNBB7ggkw3EZaLdsTJvI%3D&reserved=0

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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=HMcMPF1jX9E6WmsmK5lNTZI%2BFPbrj0cz6jU44P2Cf8E%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=NKf4X4BqUoS88S3YZ9ovlzVQWF2mHxAhHECfopYwGYU%3D&reserved=0.

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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Mew8YY0As0U63du5DNR%2BoFEj3Z%2BScNWcoX4nMltXwwA%3D&reserved=0

[https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.medtronsoftware.com%2Fimg%2FMedtronMinilogo.bmp&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=1SsEWaXGe5bCmcyc2KShYa1T1WqpF7x%2BqtahmwxAMMs%3D&reserved=0]
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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.medtronsoftware.com%2F&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=8KwQ3gOGfTP3uRT6E5S2v2hiC7%2BnNJ7wNIRYx4zW8MU%3D&reserved=0

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.
--
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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=HMcMPF1jX9E6WmsmK5lNTZI%2BFPbrj0cz6jU44P2Cf8E%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=NKf4X4BqUoS88S3YZ9ovlzVQWF2mHxAhHECfopYwGYU%3D&reserved=0.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=04%7C01%7C%7C8b8ace1f21ca4da6c10d08d9a464490b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637721572906493933%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Mew8YY0As0U63du5DNR%2BoFEj3Z%2BScNWcoX4nMltXwwA%3D&reserved=0