Re: oracle date format model

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 01 Jan 2005 00:03:09 GMT
Message-ID: <1NlBd.5282$yV1.671@newssvr14.news.prodigy.com>

tncc wrote:

> There is such a statement made by the oracle9i documentation (under Format
> model of SQL Reference):
> ***quote:***
> Oracle returns and error if an alphanumeric character is found in the date
> string where punctuation character is found in the format string. For
> example:
>
> TO_CHAR(TO_DATE('0297', 'MM/YY'), 'MM/YY')
>
> returns an error.
> *********
> However, I tried:
>
> select TO_CHAR(TO_DATE('0297', 'MM/YY'), 'MM/YY') from dual
>
> and there is no error returned. Instead the date is displayed properly
> (feb, 1997).
>
> What is meant by the above statement?
>
>

The doc also says the following, which explains why the example works, it looks like you have found a documentation inconsistency:

"The following additional formatting rules apply when converting string   values to date values (unless you have used the FX or FXFM modifiers in the format model to control exact format checking):

" * You can omit punctuation included in the format string from the date string if all the digits of the numerical format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY."

Example of the type of error the doc you found was warning against:

  select TO_CHAR(TO_DATE('297', 'MM/YY'), 'MM/YY') from dual

                          *

ERROR at line 1:
ORA-01843: not a valid month

Example where inexact format matching works OK:

select TO_CHAR(TO_DATE('2/97', 'MM/YY'), 'MM/YY') from dual;

TO_CHAR(TO_DATE('2/97','MM/YY'

---

02/97

-Mark Bole Received on Fri Dec 31 2004 - 18:03:09 CST