I understand it I would have to manually convert my YYYYMMDD numeric data
into a YYYY-MM-DD string.
You have to convert a numeric date into a Valid character representation of
a date or timestamp.
The easiest way to convert a numeric date in the format YYYYMMDD into a real
date with the SQL Date Function is:
Select Date(Digits(YourNumDate) concat '000000')), Cast(Digits(YourNumDate)
concat '000000' as Date)
From YourTable;
The easiest way to create your own SQL Function for converting a numeric
date into a real date (including error checking) is:
Create Or Replace Function YourSchema.NUM2DATE(DATENUM Decimal(8, 0))
Returns Date
Language Sql
Deterministic
Reads Sql Data
Begin
Declare Continue Handler For SQLEXCEPTION
Return Date('0001-01-01') ;
Return Cast(Digits(DATENUM) CONCAT '000000' As Date) ;
End;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark
Bonges
Sent: Montag, 18. September 2017 19:03
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: UDFs for Date conversions
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
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
digest list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
------------------------------
End of RPG400-L Digest, Vol 16, Issue 494
*****************************************
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
