Re: UDFs for Date conversions

SQL date function does take a numeric value, but it is not a "date" but
the number of days since January 1, 0001

SELECT date(736590) FROM sysibm.sysdummy1
returns   2017-09-18 


On 09/18/2017 01: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

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --