AW: AW: Date problem

Hi,

Dates are always binary values. This can be checked with the following SQL
statement:
Select  HEX(MyDate), MyDate
   from MySchema/MyTable

Date formats are only used to make this binary values readeable.
Even if you use a 2 digits date format with SQL (STRSQL --> F13 --> DatFmt =
*MDY), you can insert dates out of the "valid range". SQL will not return an
error.
If you get some problems with embedded SQL in RPG, you'll get an RPG-Error
RNX0114?.

Normally SQL is smart enough to detect a valid date if it is character
representation in one of the following formats:
'YYYY-MM-DD', 'MM/DD/YYYY', 'DD.MM.YYYY' (independend which date format is
used in the job!)
You also can mix different representations in the same SQL-statement (even
if this makes not much sense)

For example:
Select *
   from MyTable
   where MyDate between '2006-01-01' and '12/31/2006'

But SQL cannot work with date formats with a two digit year:
The following SQL-Statement will return an error:
select date('06-06-15')
from sysibm/sysdummy1

in interactive SQL (Release V5R2M0)
--> CPF5035 - 17 - Data mapping Error

iSeries Navigator seems to convert a character representation into a date,
if the representation and date separators are equal to the date format and
separators specified in JDBC-Setup. (otherwise 0001-01-01 is returned!)

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: Donnerstag, 15. Juni 2006 14:43
An: RPG programming on the AS400 / iSeries
Betreff: Re: AW: Date problem


The OP was dealing with 6 digit dates YYMMDD.  Does SQL support that, or
do you need something like RPG's 1940 guesstimator?

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





"HauserSSS" <Hauser@xxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
06/15/2006 05:56 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
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.



--
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.