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.

The first two queries are identical. The format mask is case **in**sensitive. `DD-MON-YY` is the exactly the same as `dd-mon-yy`. The last one however is strange. — , Sep 17 '15 at 07:59
it would seem that `TO_DATE` ignores characters in the input string and mask that it doesn't care about. Try `SELECT 1 FROM DUAL WHERE TRUNC(SYSDATE) = TO_DATE('17SEP15','DD!MON@YY')` — ninesided, Sep 17 '15 at 08:03
---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for HPUX: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production — user1502952, Sep 17 '15 at 08:05
So if this means that it's case sensitive , ignores the characters in between other than date,month,year and sometimes even string or number diff like DD-MON-YY and DD-MM-YY both return results, only the placing of date, month,year matters — user1502952, Sep 17 '15 at 08:14
In addition to what everyone else has said, why are you specifying your date with 2-digit years? Surely in this post-y2k era it's the norm to use all 4 digits? Why take the risk? — Boneist, Sep 17 '15 at 08:34

ninesided · Accepted Answer · 2015-09-17T08:33:20.890

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')

In addition to this, there is "fx", which means the string must exactly match the format model. See: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00216 — Boneist, Sep 17 '15 at 08:33
I think I have found my answer: select 1 from dual where trunc(sysdate) = TO_DATE('17/SEP/15', 'FXDD-MON-YY'); ( returns error) as well as this select 1 from dual where trunc(sysdate) = TO_DATE('17-SEP-15','FXDD-MM-YY'); — user1502952, Sep 17 '15 at 08:44
Thanks Boneist for the fx information, heard this for the first time — user1502952, Sep 17 '15 at 08:52

Date format not recognized while returning results

1 Answers1