Re: can this reject bad data?

If you are going to write it into a UDF anyway rewrite it from scratch.
Make it really simple.
Get the digits in the right order.
Use IBM bif's to convert to date.
Use SQL error trapping to handle the exceptions.
Do not try to calculate leap dates, etc on your own. Sure you can, but
why bother?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Needles,Stephen J" <SNEEDLES@xxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 05/08/2014 08:32 AM
Subject: RE: can this reject bad data?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



John,

Just a challenge. I didn't like that you couldn't get this result simply
using the TIMESTAMP_FORMAT scalar alone, so I wanted to see what it would
take to clean up the resulting data so that the converted number could be
recognized and worked with. Using the scalar alone made the result field
difficult to test against (see Rob's second post to this thread).

I wrote it for fun. I excluded RPG because I wanted to exercise my SQL
skills.

The OP needed to ID dates in numeric fields, this does that.

Our shop won't allow downloaded, free code without significant vetting.
One way around this is to write it myself.

Now that I've got this written, I thought to wrap the logic into a UDF
because our shop has these 8-digit numbers that represent dates in every
table. Converting these to timestamps makes date arithmetic much easier,
whether the language is RPG or SQL.

Steve Needles


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
John Yeung
Sent: Wednesday, May 07, 2014 3:58 PM
To: Midrange Systems Technical Discussion
Subject: Re: can this reject bad data?

Hmm...

On Wed, May 7, 2014 at 4:19 PM, Needles,Stephen J
<SNEEDLES@xxxxxxxxxxxxxxxx> wrote:

I wanted to solve this wholly within SQL rather than use RPG.

Any particular reason why?

I am completely with you if you just want to challenge yourself.
Exercise your brain and all that. But then you say this:

Probably not as clean as idate (didn't look at it), but it does the
job. I think that I'll wrap this in a UDF.

So, you need this for your live system. For work. Presumably at an
actual business.

Tell me again why RPG was not an option?

John
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________
This communication, including attachments, is confidential, may be subject
to legal privileges, and is intended for the sole use of the addressee.
Any use, duplication, disclosure or dissemination of this communication,
other than by the addressee, is prohibited. If you have received this
communication in error, please notify the sender immediately and delete or
destroy this communication and all copies.

TRVDiscDefault::1201
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.