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?

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

<tncc> wrote in news:41d5dc67_3@rain.i-cable.com:

> 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?
>
>
>

How in the world do you get "feb, 1997" returned by a mask of 'MM/YY'?

I suspect your actual mask was 'mon, YYYY'

Iana, look again at the Mark's example. He has an explicit to_char
around the to_date and the format mask for the to_char is 'MM/YY' so
02/97 is exactly what should have been returned.
HTH -- Mark D Powell --

"Mark D Powell" <Mark.Powell@eds.com> wrote in
news:1104702715.541588.236950@c13g2000cwb.googlegr oups.com:

> Iana, look again at the Mark's example. He has an explicit to_char
> around the to_date and the format mask for the to_char is 'MM/YY' so
> 02/97 is exactly what should have been returned.
> HTH -- Mark D Powell --
>

OP posted the following:

>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).
^^^^^^^^^^^^
I was/am questioning from where "feb, 1997" was obtained?

IANAL_VISTA wrote:

> "Mark D Powell" <Mark.Powell@eds.com> wrote in
> news:1104702715.541588.236950@c13g2000cwb.googlegr oups.com:
>
[...]
> OP posted the following:
>
>
>>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).
>
> ^^^^^^^^^^^^
> I was/am questioning from where "feb, 1997" was obtained?
>
>

I assumed it was not meant to be the literal output, but rather a
parenthetical colloquiality to support the main point, namely that there
was no error despite the documentation's claim that there would be one.

-Mark Bole

The "feb, 1996" is just the English version of what appeared. It would
have been better if "t" would have posted the actual result. Obviously
it could be confusing otherwise.

-- Mark D Powell --