Re: UDFs for Date conversions

Hi Mark

A very powerful and flexible yet simple SQL function is TIMESTAMP_FORMAT. In the example you give, of a YYYYMMDD number, you would co this:

TIMESTAMP_FORMAT(char(Yyyymmdd), 'YYYYMMDD')

and what you get back is a timestamp with time of midnight, start of day.

To get a date, wrap the above in the DATE() function of SQL.

The nice thing is, you don't need to put in all the separators. Just make the number into a character type.

There are a variation or 2 on that function, I find it extremely useful in my work here.

As to whether iTime is in iDate, if it was Alan who said that, he should know - he created it.   :)

HTH
Vern

On 9/18/2017 12:02 PM, Mark Bonges wrote:

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