Re: TIMESTAMP_FORMAT

[John Yeung <gallium.arsenide@xxxxxxxxx>]

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.