RE: SQL loval date/timestamp

[Joel Cochran <jrc@xxxxxxxxxx>]

Here is the basic layout of the program in psuedo-code:

d mainDS        e ds                  extname( MYFILE )

 // prepare and declare the cursor...

c/exec SQL
c+  fetch next from mainCursor into :mainDS
c/end-exec

The layout of MYFILE includes a Date field (defined as "L" in the DDS). 
Since Date fields themselves do not have data types this should not be
an issue.

Just to be sure I checked the compiler settings for CRTSQLRPGI and
DATFMT is *JOB.  Since the system always defaults to *ISO I assumed that
so would the program ... BUT ... when I DSPSYSVAL(QDATFMT) it says that
the value is MDY.  In fact, the option list doesn't even include ISO.

I think this must be the problem, maybe I need to specify *ISO when I
create an SQLRPG module.  This is something you do not have to do in
RPGIV, so to say this bothers me a little is an understatement.  I'm
going to try and test this some more today and I'll share the results.  

Thanks Eric,

Joel
http://www.rpgnext.com


On Mon, 2003-10-13 at 13:29, DeLong, Eric wrote:
> Joel,
> 
> If you have a date-type that displays four digit years (*ISO, as in your
> example), then the valid range of years is extended.  Only two digit year
> formats suffer the limitations you describe.
> 
> Check the field you are fetching into, and I'll bet it's *MDY, or one of the
> other limited formats.
> 
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
> 
> 
> 
> -----Original Message-----
> From: Joel Cochran [mailto:jrc@xxxxxxxxxx]
> Sent: Friday, October 10, 2003 1:56 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL loval date/timestamp
> 
> 
> Hi guys, 
> 
> Sorry I haven't gotten back to this yet, I've been out of town since
> Tuesday.
> 
> I have some traditional, DDS based, PFs with Date fields that may or may
> not get populated at the initial write.  If they do not get populated,
> then their default value is '0001-01-01' as one might expect.  However,
> what I've run into is that in an SQLRPGLE program, when I try to fetch a
> record that has one of these fields in it, I get an SQL error saying "A
> Date or Timestamp is Invalid" and the record is NOT fetched into the
> DS.  If I put a date value in the record then it processes as desired.
> 
> I remember hearing this from other people, and I remember reading that
> the lowest Date SQLRPGLE will process is like '1940-01-01' or something
> like that, but I can't seem to find the reference again.  That way, when
> I write a record I can insert a date that SQL will process, and if I
> know what it is I can programmatically ignore it.
> 
> Thanks,
> 
> Joel
> http://www.rpgnext.com
> 
> 
> On Tue, 2003-10-07 at 18:02, Buck wrote:
> > > However, when we display the column that
> > >does have a null value, the display
> > >shows '0001-01-01'.
> > 
> > That depends on how you display the column.  Some IBM utilities will show
> > you *LOVAL out of 'courtesy' I suppose.  In earlier releases I used to be
> > able to set a field value, update the record, set NULL, update the record,
> > CHAIN to a new record, CHAIN to the first record and see the original
> value,
> > despite it being NULL.
> > 
> > The moral is that one can't depend on the value of a field when it is
> NULL.
> >   --buck
> > 
> > 
> > 
> > _______________________________________________
> > 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.