The ERP I last worked on had everything as CYMD. For the SQL processes, I just wrote a UDF and trapped invalid conversions - returning null or 0 as I recall. There were conversions for numeric, character, formatted, unformatted.
Date tables are great and I used one extensively. However, when there are a large number of dates in a query, all the joins get cumbersome.
CYMDTODATE(Ship_Date) is pretty self-explanatory.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of John Yeung
Sent: Tuesday, November 7, 2023 10:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 numeric to date conversion
On Tue, Nov 7, 2023 at 7:23 AM Rob Berendt <robertowenberendt@xxxxxxxxx> wrote:
Lots of nice suggestions. The thing I like about using Alan Campin's idate
is that it also has error trapping built in. For example, if the number is
20230931 that would be invalid. I believe idate would pass it back as a
null. You can use the other suggestions but you have to build in your own
error trapping.
Note that Alan now recommends using a date table. That lets you
leverage the SQL engine for both performance and error checking
(invalid dates aren't in the table).
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://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.
midrange.com
