Date format not recognized while returning results

Question

select * from table where trunc(value1)=to_date('25-AUG-15','DD-MON-YY');

This is fine

select * from table where trunc(value1)=to_date('25-AUG-15','DD-Mon-YY');

This returned as well although the valid value should be 25-Aug-15

Even this works,

select * from table where trunc(value1)=to_date('25/AUG/15','DD-MON-YY');

result is returned

And this,

 select * from table where trunc(value1) = to_date('25-AUG-15', 'DD-MM-YY');

result is returned

but this works very well

 select * from table where trunc(value1) = to_date('25-AUG-15','MMDDYY');

it checks for the month, which is not found and returns error(well parsed!!!)

Why is that format specifier is not performing strict check on the date value supplied?

Thanks.

Answer 1

This seems to be Oracle trying to do you a solid and make it easy to parse dates into something sensible. I can't find any documentation to support this, but not only is the format mask case insensitive, but it would appear that any sequence of non-alphanumeric characters that are not a control characters that appear in either the input string or the format mask are treated as required wildcards, such that from Oracle's perspective:

TO_DATE('17!!!SEP£££15', 'DD$$$MON***YY')

is identical to:

TO_DATE('170915', 'DDMONYY')

If the number and position of the wildcards in your input don't match those of the format mask, you'll get a exception relating to the next token it is looking for:

TO_DATE('17!!!!SEP-15', 'DD-MON-YY')

ORA-01843: not a valid month

However, it gets even more weird when you look at how the wildcards in the format mask are interpreted. It seems that between tokens, any sequence of non-alphanumeric characters is treated as a single but optional wildcard, such that:

TO_DATE('17-SEP-15','DD----------------MON-YY')

is the same as:

TO_DATE('17-SEP-15','DD-MON-YY')

and because the wildcards in the format mask are option, also has the same effect as:

 TO_DATE('17-SEP-15','DD-MON-YY')