AW: Date problem

Hi,

using SQL to convert numeric values into a real date is not quite as hard,
but I agree it would be better to create a UDF (user defined function) and
use it where ever needed.

Here is the SQL-Statement to create an UDF to convert numeric values into
real dates (passing an invalid date will return '01/01/0001':
Create Function MySchema/CvtNumToDate
       (DateNum Decimal(8, 0) )
       Returns DATE
       Language SQL
       Deterministic
       Contains SQL
       Returns NULL on NULL Input
       No External Action
       Set Option DbgView = *Source,
                  DatFmt  = *ISO
  BEGIN
       Declare CvtDate DATE ;
       Declare InvalidDate Condition For '22007' ;
       Declare Continue HANDLER for InvalidDate
               set CvtDate = '0001-01-01';
       Set CvtDate = Date(Substring(Digits(DateNum), 1, 4) Concat '-' Concat
                          Substring(Digits(DateNum), 5, 2) Concat '-' Concat
                          Substring(Digits(DateNum), 7, 2 )) ;
       Return CvtDate;
 END;

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rob@xxxxxxxxx
Gesendet: Mittwoch, 14. Juni 2006 23:04
An: RPG programming on the AS400 / iSeries
Betreff: RE: Date problem


SQL's capabilities to convert a numeric to a date are rather cumbersome. I
would look at a UDF (User Defined Function)

Search for my name in
http://faq.midrange.com/data/cache/185.html


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





jmichael.smith@xxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
06/14/2006 04:00 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Subject
RE: Date problem






Concur > Might be a bit hard in a DDS logical file.

I don't know how to accomplish this. Ideas> Could be done in a SQL view
though.

Michael


----------------------------------------------------------------------------
--
This message contains information from Certegy, Inc which may be
confidential and privileged.  If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited.  If you have
received this transmission in error, please notify by e:mail
postmaster@xxxxxxxxxxxx
============================================================================
==

--
This is the RPG programming on the AS400 / iSeries (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.


--
This is the RPG programming on the AS400 / iSeries (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.