Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
| От | Chad Wagner |
|---|---|
| Тема | Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates) |
| Дата | |
| Msg-id | 81961ff50702171957s36f7fdd8id0b2266c50948d2b@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates) ("Brendan Jurd" <direvus@gmail.com>) |
| Список | pgsql-hackers |
On 2/17/07, Brendan Jurd <direvus@gmail.com> wrote:
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
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/
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/
В списке pgsql-hackers по дате отправления: