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

On 19-Jul-2014 10:03 -0500, James Newman, CDP wrote:

I have the following code in an RPG ILE program:

/EXEC SQL
+ DECLARE C1 CURSOR FOR
+ SELECT MIN(HIDATE)
+ FROM MHIST
/END-EXEC

FWIW, all my date fields are ISO (YYYY-MM-DD).

As in, what was set in database file field\column definitions as the Date Format, as established via the DDS compile or defaulted in the SQL?

The program was blowing up when I tried to move the result to another
date field, saying I had an invalid date. I looked at the dump and
the date field contained "++++++++++".

The hex output may, like with invalid decimal data, show the value of the variable\field; i.e. the hex output for the value is unlikely to be x'4E4E...', and instead appear something like x'F1F9F1F560F0...'.

Ensure the Date Format of the program and\or variable [to which the value is being assigned] is set to ISO. The H-spec can include DATFMT(*ISO) for the program, and\or for any individual variable declarations. As an SQL RPGLE program, there is also the SQL date format to establish the format preference for the SQL work\variables; e.g. SET OPTION DATFMT=*ISO

I did STRSQL, ran the command, and got the same thing. I used Query
and it shows the minimum (oldest) date as "1915-01-05". Ran the SQL
version again but this time didn't ask for MIN. Instead asked to see
the data and sorted by date. All the dates prior to 1940-01-01 are
"++++++++++".

In STRSQL there is a Date Format option setting in the F13=Services.

I know I can sort the records by date, SETLL, and READ but I figured
this would be faster and more efficient.

For just the one inquiry, probably somewhat of a wash. The SQL work will intuitively involve much more overhead in a one-to-one comparison.

Is there a "Yes, I really have data before 1940 that I'd like to
see" switch somewhere in SQL that I need to flip?

The most explicit manner is to request the formatted character form for the retrieved data; e.g. using the CHAR or VARCHAR casting scalar to explicitly request the ISO date format for the /character-date/ result, recognizing of course, that the result is returned as a CHAR versus a DATE data type: SELECT CHAR( MIN(HIDATE) , ISO )

Or more generally, request that the program variables and SQL requests should reflect a particular date format; i.e. setting the DATFMT attributes(s) within the host language and the SQL, to any of the four-digit-year formats.