Re: UDFs for Date conversions

["Mark Bonges" <mrb1@xxxxxxxxxxxxxxxxxxxxxxxx>]

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 and
times.
> Dates are stored in 8 zoned or 9 packed numeric fields in YYYYMMDD format.
> Times in a 6 zoned HHMMSS format. I'm thinking about creating a few UDFs
so
> that I can more easily convert them to SQL dates. Then I started thinking
> that this is probably a very common problem and there is probably a
solution
> 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
>
> Thanks!
> Mark



------------------------------

Subject: Digest Footer