Thanks everyone for your responses. A few responses to those responses:
"iDate is what 99% of the people on these lists use."
This is exactly what I was hoping to find. Thanks!!
"SQL already has functions for this"
The DATE() function in SQL does not take in a numeric value. If I were to
use this function as I understand it I would have to manually convert my
YYYYMMDD numeric data into a YYYY-MM-DD string. I've done this before when
the need arises, but it's very cumbersome. If there is an easier way to use
the SQL date functions please let me know.
Create a date file:
We already have a date file, but it doesn't contain a Date datatype column
at this time. Adding one would be a good idea and making a time table as
well as you suggest would also not be a bad idea. Not as easy as having a
UDF but much easier than substrings. I also like that it would perform
better for large tables. However you mention to write my own I would need
the complete leap year rules, I don't see that as the case. The function
would build a timestamp character string and then use the %timestamp built
in function to create the timestamp.
"iDate includes iTime"
I just downloaded iDate and I don't see iTime anywhere in the package I
downloaded. However especially using iDate as a framework adding a time
function shouldn't be difficult.
Thanks again to everyone who responded.
On 15/09/2017 21:49, Mark Bonges wrote:
My shop like many I'm told in the IBM i world uses numeric dates andtimes.
Dates are stored in 8 zoned or 9 packed numeric fields in YYYYMMDD format.so
Times in a 6 zoned HHMMSS format. I'm thinking about creating a few UDFs
that I can more easily convert them to SQL dates. Then I started thinkingsolution
that this is probably a very common problem and there is probably a
out there already. Does anyone know of one?
If not maybe I'll make them and put the code on github or something.
I'm thining of three functions:
1. numeric date and time in, timestamp out
2. numeric date in, date out
3. numeric time in, time out
Subject: Digest Footer