Re: oh dear ...

I said:
> This worked in 7.3:
> regression=# select '1999-jan-08'::date;
> ERROR:  date/time field value out of range: "1999-jan-08"
> HINT:  Perhaps you need a different "datestyle" setting.

> Setting DateStyle to YMD doesn't help, and in any case I'd think that
> this ought to be considered an unambiguous input format.

This appears to be an oversight in the portions of the datetime code
that we recently changed to enforce DateStyle more tightly.
Specifically, DecodeNumber was rewritten without realizing that it was
invoked in a special way when a textual month name appears in the input.
DecodeDate actually makes two passes over the input, noting the textual
month name in the first pass, and then calling DecodeNumber on only the
numeric fields in the second pass.  This means that when DecodeNumber is
called for the first time, the MONTH flag may already be set.  The
rewrite mistakenly assumed that in this case we must be at the second
field of an MM-DD-YY-order input.

I propose the attached patch to fix the problem.  It doesn't break any
regression tests, and it appears to fix the cases noted in its comment.

Opinions on whether to apply this to 7.4?

            regards, tom lane

*** src/backend/utils/adt/datetime.c.orig    Thu Sep 25 10:23:13 2003
--- src/backend/utils/adt/datetime.c    Fri Nov 14 19:22:47 2003
***************
*** 2553,2561 ****
              break;

          case (DTK_M(MONTH)):
!             /* Must be at second field of MM-DD-YY */
!             *tmask = DTK_M(DAY);
!             tm->tm_mday = val;
              break;

          case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):
--- 2553,2577 ----
              break;

          case (DTK_M(MONTH)):
!             /*
!              * There are two possibilities: we are at second field of
!              * MM-DD-YY (with DateOrder MDY), or we are at the first
!              * numeric field of a date that included a textual month name.
!              * We want to support the variants MON-DD-YYYY, DD-MON-YYYY,
!              * and YYYY-MON-DD as unambiguous inputs.  We will also accept
!              * MON-DD-YY or DD-MON-YY in either DMY or MDY modes, as well
!              * as YY-MON-DD in YMD mode.  Hence:
!              */
!             if (flen >= 3 || DateOrder == DATEORDER_YMD)
!             {
!                 *tmask = DTK_M(YEAR);
!                 tm->tm_year = val;
!             }
!             else
!             {
!                 *tmask = DTK_M(DAY);
!                 tm->tm_mday = val;
!             }
              break;

          case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):