Re: Converting a *mdy date to *iso for SQLRPGLE programs

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Arthur,

Alan Campin has a nice UDF called iDate that allows you to convert almost
any decimal or char field (providing it is a valid date, of course) to a
DATE field.

Check out it here:

http://www.think400.dk/downloads.htm

<http://www.think400.dk/downloads.htm>HTH,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Apr 29, 2010 at 10:05 AM, Arthur Marino <amarino@xxxxxxxxxxxx>wrote:

> Hi All,
>
>
>
> I realize this doesn't belong in the RPG list, but I just figured this out
> and I'm excited about it. Besides, you can use it to prepare generic SQL
> statements in an RPG program (which is what I need it for), so maybe it's
> germane.
>
>
>
> (If everybody already knew how to do this, I wish someone would've let me
> know. And if there's a better way, please let me know that too)
>
>
>
> SELECT * FROM <file-name> WHERE
>
> DATE(CASE WHEN CHARACTER_LENGTH(mmddyyDATE) = 5 THEN
>
>           '0' CONCAT SUBSTR(CHAR(mmddyyDATE),1,1) CONCAT '/'
>
>               CONCAT SUBSTR(CHAR(mmddyyDATE),2,2) CONCAT '/'
>
>               CONCAT SUBSTR(CHAR(mmddyyDATE),4,2)
>
>          ELSE SUBSTR(CHAR(mmddyyDATE),1,2) CONCAT '/'
>
>               CONCAT SUBSTR(CHAR(mmddyyDATE),3,2) CONCAT '/'
>
>               CONCAT SUBSTR(CHAR(mmddyyDATE),5,2)
>
>    END)
>
>  BETWEEN  '2008-10-01'  AND '2010-10-31'
>
>
>
> Arthur J. Marino
>
> RockTenn Corporation
>
> ________________________________
> This message, including its attachments, constitutes an electronic
> communication within the meaning of the Electronic Communications Privacy
> Act, 18 U.S.C. Section 2510. This message is the confidential property of
> RockTenn, and disclosure is strictly limited to recipients intended by the
> sender. Unless previously authorized in writing, this message does not
> constitute an offer, acceptance, or agreement of any kind. This message may
> contain confidential attorney-client privileged information and attorney
> work product. If you are not the intended recipient, or responsible for
> delivering e-mail to the intended recipient, you are advised that use,
> dissemination, forwarding, printing, or copying of this message is STRICTLY
> PROHIBITED. If you receive this message in error, please notify the sender
> immediately by return e-mail and delete the message entirely from your
> system. Sender is not liable for damage caused by viruses transmitted with
> this message, or errors or omissions in!
>  content caused by transmission.
> (c) RockTenn, Norcross, GA.
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://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.