Re: Date not valid, V7R1 Dynamic SQL

[CRPence <CRPbottle@xxxxxxxxx>]

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.