Re: Date not valid, V7R1 Dynamic SQL

On 03-Mar-2015 16:00 -0600, Cyndi Bradberry wrote:

I have a field in a file defined as a date data type, *ISO. I
display the date on the screen in *USA format. The problem comes when
trying to retrieve the record via a subfile built thru SQL. I get no
record due to the date being invalid because the date is 2099-12-01.
According to the RPG & DDS books, date in either USA or ISO formats
can go to 9999 in the year. If I change the date to 2039, the record
is retrieved perfectly.

Message ID . . . . . . : CPF5035 <<SNIP>>
Message . . . . : Data mapping error on member SMKCOMIT.
Cause . . . . . : A data mapping error occurred on field
SMKCOMIT_1.CEXPDT in record number 0, record format *FIRST,
member number 1, in member SMKCOMIT file SMKCOMIT in
library HOLLIB, because of error code 18. <<SNIP>>
18 -- There is data in a date, time, or timestamp field
that is not valid. <<SNIP>>


As defined in the display file:
A #2EXPDT L O 9115DATFMT(*USA)


As defined in the physical file:
A CEXPDT L TEXT('Expiration Date')
A DATFMT(*ISO)

What am I missing ? RTM links appreciated.

The SQL as an effective operating environment, has its own distinct DATFMT() specification; what the definition in the file is, for the SQL run-time, is immaterial.

While the literal value '2099-12-01' is a valid date string recognizable as one of a standard format [as either *ISO or *JIS; they are the same for Date Formatting], that date value [or string as date value] is not valid within the 100-year window, for any date that must be processed with a two-digit year format [e.g. one of *YMD, *DMY, *MDY].

Because the SQL defaults to the DATFMT(*JOB), the SQL will always begin the environment with one of the allowed values for the job's DATFMT() specification which includes only: *MDY, *YMD, *DMY, and *JUL

The SQL must be informed that dates in character string format [i.e. external presentation] must be represented with one of the /standards/ four-digit year formats. Allowing for the full four digits avoids the ambiguity that causes a data mapping error per the prohibition of years outside the window of 1940 to 2039. With embedded SQL, code the SET OPTION statement to include the DATFMT=datfmt-option where the datfmt-option is one of: *ISO, *EUR, *USA, or *JIS
<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzsoption.htm>

Note: the CRTSQLxxx commands [e.g. Create SQL RPG Program (CRTSQLRPG)
which may be what is used in the described scenario] provide a DATFMT() parameter, and that is as I recall, the same specification for the pre-compiler, as that which would be effected alternatively by the SET OPTION statement.