On 2/17/07, Brendan Jurd <[EMAIL PROTECTED]> 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/