On Fri, Sep 15, 2017 at 4:49 PM, Mark Bonges
<mrb1@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:
My shop like many I'm told in the IBM i world uses numeric dates and times.
Dates are stored in 8 zoned or 9 packed numeric fields in YYYYMMDD format.
Lucky! Where I work, our legacy dates are in three or four separate
2-digit fields. Or a 2-digit century field and a 6-digit *MDY field.
But yes, what you have is very common.
Times in a 6 zoned HHMMSS format.
I don't think times are covered by IDATE. I'll get back to the issue
of times in a moment.
As for dates only: I have no problem recommending IDATE. I think Kevin
is right that it is the most common way to handle dates among folks on
this list.
But some of us like having a date file. That is, just a table with one
record per date, that is JOINable in SQL. This is what I use here
because (1) our crazy dates are not directly convertible with IDATE,
so there would need to be an extra step anyway; (2) in principle, this
could perform better than a UDF in situations where the UDF would need
to be called for every record in a large table (such as in a WHERE
clause filtering by date); and (3) it could be handy for other things,
like additional formats (Excel anyone?) or days of the week or month
names, etc.
The date file I built has 146097 records, and runs from 1900-01-01
through 2299-12-31. This is a smallish file by today's standards.
So, back to times: If you're only interested in precision to the
second, you could do a time file with 86400 records (possibly plus a
couple more if you want to allow both forms of midnight and leap
seconds).
There isn't a good file-based answer for timestamps. And I don't think
we really talk about converting to or from timestamps all that often
here. So maybe it turns out you need to write that one yourself after
all. (And while you're at it, you probably might as well do the others
you were thinking of. None of these is particularly hard; they just
need proper knowledge, such as the COMPLETE leap year rules, and
sufficient care and testing).
John Y.
midrange.com
