Oracle TO_DATE NOT throwing error

Question

I just spotted curious behaviour of oracle TO_DATE function when used with format_mask parameter.

Basically, what I see is that in one case it ignores given format mask, and parses input with its own mask, and in other cases it throws an exception.

Example I expected behaviour - error thrown:

SELECT TO_DATE('18-02-2016', 'DD/MON/YYYY') FROM dual

ORA-01843: not a valid month

Example II unexpected behaviour - date parsed:

SELECT TO_DATE('18-feb-2016', 'DD/MM/YYYY') FROM dual

February, 18 2016 00:00:00

I cannot see any remark of this in docs, so I am wondering if this incostincency is by design or is it bug or maybe I am not understanding something correct?

Edit: Looking at answers I can agree that it is most probably by design. But what is done here looks dangerously "automagical" to me.

What if format will be interpreted (guessed by oracle) incorrectly? Is there any documentation on what is exactly happening here, so I can be sure that it is safe?

My question would be then - can I turn it off? Is my only option validating format on my own?

Answer 1

See the table here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#g195479

It is part of the String-To-Date Conversion Rules section of the Datetime format model. In the case of MM if there is no match, it attempts for MON and MONTH. Similarly if you specify MON and it does not find that, it attempts MONTH. If you specify MONTH and it cannot find that, it attempts MON, but it will never attempt MM on anything except MM.

In response to the question: Can I turn it off? The answer is, Yes.

You can do that by specifying FX as part of your formatting.

SELECT TO_DATE('18/february/2016', 'FXDD/MM/YYYY') FROM dual;

Now returns:

[Error] Execution (4: 16): ORA-01858: a non-numeric character was found where a numeric was expected

Whereas the following:

SELECT TO_DATE('18/02/2016', 'FXDD/MM/YYYY') FROM dual;

Returns the expected:

2/18/2016

Note that when specifying FX you MUST use the proper separators otherwise it will error.

Answer 2

This is by design. Oracle tries to find deterministic date representation in the string even if does not comply with the defined mask. It throws the error only it doesn't find deterministic date or some required component of the date is missing or not resolved.