A date field is always stored as scaliger no. The date format is only used
to make the scaliger no readable. The format to be used depends on the
current date format within the job.
A date field in an SQL table is defined simply with the data type DATE
(there is NO format keyword!).
When inserting a date into a date field in a table, SQL can convert
character sting representing a date in one of the following formats
YYYY-MM-DD, DD.MM.YYYY, MM/DD/YYYY. The date format to be used for the
character string does not need to match the current job's date format.
Just try the following:
CREATE TABLE YourSchema.YourTable
(YourDate Date Not NULL Default);
Insert into YourSchema.YourTable
Values('2015-12-31'), ('31.12.2015'), ('12/31/2015');;
When using date or time fields in RPG, the SQL precompiler generates
additional workfields. The date and time format for these work fields is NOT
adopted from the defined RPG host variables, but from the compile options
DATFMT, TIMFMT. The default value for DATFMT is *JOB and quite often the
current job's date format is a date format with a 2 digit year.
If your original RPG variable is defined with date format *ISO and
initialized with the *Loval (0001-01-01), and you compile the embedded SQL
program with default options, your RPG program will crash as soon as the
host variable content is moved into the additional precomiler work field.
The easiest way to avoid those problems is to predefine the date format (to
any of an date format with a 4 digit year) in an SET OPTION statement
(H-specs for SQL) at the beginning of your source code. Example:
DCL-S YourDate DatFmt(*ISO);
Exec SQL Set Options DatFmt=*ISO;
YourDate = *Loval;
Exec SQL Insert Into YourSchema.YourTable Values(:YourDate);
YourDAte = D'2015-12-31';
Exec SQL Insert into YourSchema.YourTable Values(:YourDate);
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Booth
Martin
Gesendet: Saturday, 26.12 2015 05:00
An: RPG programming on the IBM i / System i
Betreff: RPG embedded SQL - how to define a date field
How do I define a date field, and populate it with ISO dates?
--
This is the RPG programming on the IBM i (AS/400 and 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.
midrange.com
