Re: Another SQL newbie question

[CRPence <CRPbottle@xxxxxxxxx>]

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