On 2/17/07,
Brendan Jurd <
direvus@gmail.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/
