Oracle TO_DATE NOT throwing error

问题:

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?

回答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.

回答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.

回答3:

I think a 02 could mean date/month/year anything, but feb would only mean one thing, a month. Also I crosschecked the same in 11g and got the same output as you got in 12c. So it has to be by oracle design.

回答4:

If you want to disable Oracle's attempt to be helpful and interpret things that don't exactly match, you can use the FX format modifier:

FX
Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

• Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.
• The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.
• Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeros.
• When FX is enabled, you can disable this check for leading zeros by using the FM modifier as well.

So your example would error with:

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

SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected

That will also require the delimiters to match exactly:

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

SQL Error: ORA-01861: literal does not match format string

If you get it right then it's OK:

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

It doesn't complain about the different case in the second example. But it will complain if you omit leading zeros;

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

SQL Error: ORA-01862: the numeric value does not match the length of the format item

... unless you also include the FM modifier:

SELECT TO_DATE('18/2/2016', 'FMFXDD/MM/YYYY') FROM dual;