Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)

On 2/17/07, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
>
> I just looked through the Oracle documentation, and it is
> conspicuously silent on the topic of invalid format patterns. Much
> like ours in fact.

On the case of the format: YYYY-MM-DD J, if J is the same date as YYYY-MM-DD
then Oracle appears to silently ignore it. But if J is not the same date as
YYYY-MM-DD then Oracle throws an error:

SQL> select to_date('2007-02-17 2454149', 'YYYY-MM-DD J') from dual;

TO_DATE('
---------
17-FEB-07

SQL> select to_date('2007-02-17 2454145', 'YYYY-MM-DD J') from dual;
select to_date('2007-02-17 2454145', 'YYYY-MM-DD J') from dual
*
ERROR at line 1:
ORA-01834: day of month conflicts with Julian date

I like your suggestion of the pattern modifier. So if a user did try
> to format with 'YYYY-MM-DD "Q"Q', we would throw an error telling them
> that the pattern is over-constraining, and they can use this pattern
> modifier (* or whatever) to single out the non-normative fields.

As for 'Q', Oracle doesn't appear to support this in terms of TO_DATE, I
believe it is for TO_CHAR only.

SQL> select to_date('2007-02-17 1', 'YYYY-MM-DD Q') from dual;
select to_date('2007-02-17 1', 'YYYY-MM-DD Q') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

SQL> select to_date('1', 'Q') from dual;
select to_date('1', 'Q') from dual
*
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

--
Chad
http://www.postgresqlforums.com/