Re: Another SQL newbie question

On 12-May-2011 17:12 , James Lampert wrote:

With further experimentation, I find that 19410101 works, but
19310101 blows up.

Hmm. According to the SQL book, DATE has exactly the 1940-2039 range
IF it's *JUL, *MDY, *DMY, or *YMD. But I'm feeding it *ISO, and a
DSPFFD on the view shows it as *ISO.

The problem is not the type, nor the column attribute. The problem is the DATFMT option defaulted in or specified in the feature [program, SQL script processor, etc.] that is trying to process date type; in this case, a date data type value RETURNed by the UDF.

Using STRSQL with F13=Services such that Date Format is *MDY would exhibit the date underflow problem for the year 1931 because the date value 1931-10-01 would be ambiguous if the SQL were allowed to present the string result as 10/01/31. To present a column DATECOL as an ISO formatted date in a session\program, irrespective of the DatFmt setting established, use the CHAR cast function with the second parameter to request the date string be formatted with a four-digit year regardless of how the feature might want to format the date; e.g.

select CHAR(DATECOL,ISO) from MyFile

Or for the literal string as a date cast back to a string, but in *USA format:

select char(date('1931-10-01'),USA) from qsys2/qsqptabl

Report result:

CHAR conversion
10/01/1931

Regards, Chuck