Re: SQL returns "++++++++++" for MIN(DATE)

On 19-Jul-2014 13:24 -0500, James Newman, CDP wrote:

<<SNIP>> I'm using CRTSQLRPGI to compile programs. Is there an easy
way to change the default for that command so I don't have to change
it each time?

Change Command Default (CHGCMDDFT) as part of proper system change-management is an option to customize the Create SQL ILE RPG Object (CRTSQLRPGI) command. Specifically for the Date Format parameter option:

CHGCMDDFT CRTSQLRPGI 'DATFMT(*ISO)'

Any source with a properly coded SQL SET OPTION DATFMT=value request that overrides the Date Format (DATFMT) to the setting required by that source, will be unaffected by the change to the default of the DATFMT() for the SQL pre-compile request. Any source without that SET statement coded [as the first executable SQL], for which the source code has a dependency on a prior default of *JOB, could be impacted by a change to the command default to something other than *JOB; i.e. if the program [source] is coded with an assumption that the default was *JOB instead of explicitly having set the date format to *JOB in the SQL SET OPTION, then having changed the command default to something other than *JOB may cause the compiled program to experience a failed assumption.

As far as I know, any dependency on the format for an RPG date variable [including those used as SQL host variables, but not the SQL variables themselves] must be handled either by an explicit DATFMT() specification on the variable, or with a DATFMT() coded in an H-spec, but the default is *ISO, so that should not typically be a problem [see msg RNF0612].

The OP suggested that the failure occurred trying "to move the result to another date field" with "an invalid date" error, but for lack of any [specific] information about the error [e.g. inq RNQ0114 per msg RNX0114 or msg SQL0181 per SQLSTATE = '22007' and SQLCODE = -181] and lack of information about the code\source and compiler specifications, the issue could have arisen due to the definitional aspect of either the RPG or the SQL; most likely the SQL, for lack of a SET OPTION or DATFMTI() specification on the compile to ensure the SQL uses something other than the default of DATFMT(*JOB).