RE: SQL loval date/timestamp

[Joel Cochran <jrc@xxxxxxxxxx>]

On Tue, 2003-10-14 at 16:09, Dave Boettcher wrote:
> Joel, 
> I am not sure I have seen all the e-mails on this subject, however, in
> SQLRPGLE I remember having to specifically use the DATFMT(*ISO) in the
> header for at least one program.  If there is a way you can look at the file
> you should be able to see what is in the date fields.  Any date should fit
> into *ISO since its year range is 0001 to 9999. 
> MDY is a different story.  So see what the actual date is in the file. JMHO.

That's right Dave, 

The original problem came about because I have Date fields in the
database that do not always get populated: as a result, many of those
dates are '0001-01-01', a valid date, but not if the SQL program thinks
the format is MDY.

I've recompiled the SQLRPGLE module using DATFMT(*ISO) and now it is
working fine.  Unfortunately I now have to either change my command
default or remember this little tidbit every stinkin' time, because this
will need to be done for every program that uses SQL to access file with
a Date field.  I can't have this blowing up just because a date field
was never set.  That's like a program not working because a numeric
field's value is 0 or a character value is blank.

<soapbox>
This is more and more becoming my pet peeve: the SQL pre-compiler and
the RPGIV teams should be joined at the hip.  I would never have had
this problem using traditional file access, but as soon as I want to add
a couple of SQL statements I have to remember a new set of rules on top
of the actual SQL.  On my peeve list now are:

1) No nested /copy statements.
2) Don't forget COMMIT(*NONE).
3) Don't get the prepare, declare, open, fetch, and close statements
physically out of order.
4) No local variables in SQL statements.
5) DATFMT(*ISO) if your DB files have "real" Date fields.
</soapbox>

Sorry for the rant but this should be easier.  I'm not saying it's easy
for the compiler team(s), but it should be easier for the programmers.

Thanks to everyone who responded.

Joel
http://www.rpgnext.com