RE: [External] Re: TIMESTAMP_FORMAT -- MIDRANGE-L

I have not yet tried Alan Campin's iDate utility having documentation which states it can handle *JDEJUL - CYYDDD Julian. Century Number (0 or 1) for J.D. Edward folks.

Reminds me of the *JDEJUL conversion offered by Howard Arner at http://www.sqlthing.com/Resources/JDDCONV.html
Howard lists the steps to create a user defined function that encapsulates this logic to display *JDE dates in mm/dd/yy format:
select DATE(DIGITS(DECIMAL(ABUPMJ +1900000,7,0))), ABUPMJ from F0101

That web page states: the code for the UDF.

CREATE FUNCTION MCEIS.JDDCONV
(JDEDATE DECIMAL(6,0))
RETURNS DATE
LANGUAGE SQL
SET OPTION DATFMT=*ISO
BEGIN
DECLARE F_OUTPUT DATE ;
DECLARE F_TEST INTEGER ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ;
SET F_TEST = 0;
SET F_OUTPUT = DATE(DIGITS(DECIMAL(JDEDATE+1900000,7,0)));
IF F_TEST = 0 THEN RETURN F_OUTPUT ;
ELSE RETURN NULL;
END IF;
END

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of John Yeung
Sent: Thursday, November 11, 2021 3:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [External] Re: TIMESTAMP_FORMAT

[WARNING]: External Email

On Thu, Nov 11, 2021 at 11:19 AM Stephen Landess <steve_landess@xxxxxxxxxxx> wrote:

I have been working with JDE for almost 40 years, so I am very familiar with all of the JDE callable routines for validating and manipulating data. In this case, my interest is in SQL scripts or SQL queries against the JDE files, and I need the simplest way to query JDE and compare jde date fields OUTSIDE of the JDE software.

Alan's iDate UDF is perfect for that!

There is definitely a lot of value in iDate, and I'm comfortable recommending it to anyone.

I think it's worth mentioning, though, that if you need to call a function on every row in a large table, it may be worth using a date conversion table instead, leveraging SQL's JOIN to do the "calculation".

Building a date conversion table is pretty straightforward. Where I work, I made one which also has things like Excel date, day of the week, day of the week names, month names, and some other awkward-to-calculate-on-the-fly stuff. I guess you could think of it sort of like iDate, but pre-materialized into table form. I made ours with 146097 records covering 1900-01-01 through 2299-12-31.

John Y.
--
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://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=04%7C01%7Celehti%40quiktrip.com%7Cd5945fb4d84d4f4793b308d9a5566f49%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637722612984385725%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=7iodMmP%2FVLNIErvctMOnTvFId3AOxUnELtmH9Vy8%2BNc%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=04%7C01%7Celehti%40quiktrip.com%7Cd5945fb4d84d4f4793b308d9a5566f49%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637722612984385725%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=TWSJcRjqMQbSM11DS2A35cFCNwG0R69QetlfQPhQc9I%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://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com%2F&data=04%7C01%7Celehti%40quiktrip.com%7Cd5945fb4d84d4f4793b308d9a5566f49%7Cbe902cb3a2b74bfd86442264fe46c43d%7C0%7C0%7C637722612984385725%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=MQMgb8Y4kWQKaFe2iXFbVlWjrtL32vXuee3anmhsYYQ%3D&reserved=0