Re: DDS date field & SQL

On 29-Sep-2015 10:26 -0600, Justin Taylor wrote:

I have a DDS PF that contains a date field defined:
A DATEFLD L DATFMT(*ISO)
A DFT('0001-01-01')


RPGLE, DSPPFM and QRY/400 are all fine with that and show the field
correctly. SQL chokes on it however. It returns a null value and a
01534 SQLSTATE (The string representation of a datetime value is
invalid).

Any ideas? Is it the DFT keyword?

The SQL has a Date Format (DATFMT) _OPTION_ similar to the Date Format (DATFMT) for the job. The typical and likely default Date Format for the SQL is the DATFMT(*JOB). The IBM i job Date Format options are [still AFaIK] restricted to 2-digit-year formats [i.e. *DMY, *YMD, *JUL, *MDY, and even *SYSVAL because that can resolve only to one of those prior-noted 2-digit-year formats], so the choice of DATFMT(*JOB) allows presentation of only the dates between 1940-01-01 until 2039-12-31 to avoid ambiguity; anything outside that range, will result in a date-data-mapping error.

For embedded SQL there is the SET OPTIONS [or the DATFMT() parameter on the CRTSQLxxx command], but other interfaces to the SQL may have a DATFMT() [or similar] parameter. Note however that the request to Start Interactive SQL Session (STRSQL) ignores whatever is the specification for that parameter, whenever re-entering\restarting an existing\saved session, per the logged msg SQL6335 "STRSQL parameters were ignored."; for an existing session, follow the recovery of that message, and effect the change instead by "press[ing] F13 to change the session attributes."