Why Value in MON format works for session Date Format being MM but not the vice versa

Arun Kumar Gupta

Hello Experts

I know very well how to use TO_DATE function with string. However could not find any reference why value passed in Mon ( or MON) format works well when month format is set to MM but not the vice versa.

When Date format for session is set to 'MM/DD/YYYY' all following values works well

12/30/1982, Dec/30/1982, Dec-30-1982

However when Date format for session is set to 'Mon/DD/YYYY' this format does not work

12/30/1982

Means "Mon" format works well even session format is "MM" for month but when Session format is "MM" then values passed in "Mon" format does not work.

Please see few examples below.

Connected to:

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

SQL> set linesize 400

SQL> SELECT banner_full from v$version;

BANNER_FULL

-----------------------------------------------------------------------------------------------------------------------

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0

SQL> alter session set nls_date_format='MM/DD/YYYY';

Session altered.

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > '12/09/1982';

HIREDATE  ENAME

---------- ----------

01/12/1983 ADAMS

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > 'Dec/09/1982';

HIREDATE  ENAME

---------- ----------

01/12/1983 ADAMS

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > 'Dec-09-1982';

HIREDATE  ENAME

---------- ----------

01/12/1983 ADAMS

SQL> alter session set nls_date_format='Mon/DD/YYYY';

Session altered.

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > '12/09/1982';

SELECT hiredate, ename FROM emp WHERE hiredate > '12/09/1982'

                         *

ERROR at line 1:

ORA-01843: not a valid month

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > 'Dec/09/1982';

HIREDATE  ENAME

----------- ----------

Jan/12/1983 ADAMS

SQL> SELECT hiredate, ename FROM emp WHERE hiredate > 'Dec-09-1982';

HIREDATE  ENAME

----------- ----------

Jan/12/1983 ADAMS

Regards

Arun

BEDE

It's best not to rely on some date format, but rather use to_date and to_timestamp with the desired format mask.

BluShadow

Let's face it, Oracle tries it's best to interpret shoddy code, and in some cases it's successful, but in others it won't work.

If you're months are numeric, use MM, if they're alpha, use MON (or whatever is appropriate JAN,FEB,MAR... => MON; January; February; March... => Month etc.)

If you pass the wrong thing, don't expect perfect results. It's as bad as people passing 2 digit years and expecting Oracle to magically know the correct century for it. *sigh*

Arun Kumar Gupta

https://community.oracle.com/tech/developers/discussion/comment/16794648#Comment_16794648

Yes I am very well aware of using appropriate formats while using strings as date suing TO_DATE function.

Just got stuck with this and could not find any reference material why does oracle behaves like this.

Seems somewhere Oracle doing that implicit conversion of passed value in "MON" (Character) format to Numeric "MM" when NLS date format is MM (Number).

However when NLS format is "MON" (Character) and passed value is in "MM" (Number) format it does not do implicit conversion.

Regards

Arun

mathguy

Your question is really two questions packed together.

The "mechanical" one is whether the observed behavior is intended (it follows specification, as - hopefully - stated somewhere in the documentation).

The "philosophical" question, even if you can find that the observed behavior is consistent with the documentation, is "why did Oracle make such a choice"?

I will leave the philosophical question aside; if I am going to object to Oracle design choices, I will start with empty strings being the same as null (and not even applied consistently everywhere). Who knows why Oracle makes certain choices. Perhaps one group of programmers doesn't talk to another, and things get put together without enough thought to consistency. Or some other reason.

But the "mechanical" question has a simple answer. Whether it makes sense or not (probably not!), the choice is documented. If the format model has MM for month, but the actual value provided doesn't match MM, Oracle will also try MON. However, if the format model provided has MON for month, but the actual string value has the month in the MM format, then no attempt to identify the month will be made; instead, the function throws an error.

This is documented here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#g195479

BluShadow

Well it's also not documented that Oracle will try and interpret different separators in the date format e.g.

SQL> select to_date('10.01.2021','DD/MM/YYYY') from dual;


TO_DATE('10.01.2021'
--------------------
10-JAN-2021 00:00:00

-- or even

SQL> select to_date('10'||chr(13)||'01'||chr(13)||'2021','DD/MM/YYYY') from dual;


TO_DATE('10'||CHR(13
--------------------
10-JAN-2021 00:00:00

but also not documented is the formats it won't work with...

SQL> select to_date('10'||chr(255)||'01'||chr(255)||'2021','DD/MM/YYYY') from dual;
select to_date('10'||chr(255)||'01'||chr(255)||'2021','DD/MM/YYYY') from dual
                       *
ERROR at line 1:
ORA-01861: literal does not match format string

Whilst Oracle will document the correct syntax and usage for things, you can't expect them to document all the exceptions as well. And whilst they do sometimes cater for small differences, they can't account for everything people will throw at them.

The long and short if it is, don't write shoddy code. If this were Microsoft SQL Server, you wouldn't even get away with a slight difference, or any sort of implicit conversion of datatypes.

mathguy

@BluShadow -

While the documentation doesn't explicitly state that by default punctuation marks used as separators are interchangeable, it doesn't explicitly state that they aren't, either. In fact, it does say that by default they are interchangeable, but it only does that implicitly:

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.

So, by implication, without the FX model modifier, punctuation is not required to "exactly match" the corresponding parts of the format model.

This is from the same link I posted earlier.

BluShadow

True, but in that case, why does chr(255) not work. 😂

Of course, many of us don't come across such issues, because we use the correct format masks for the data we're processing. 👍️

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16794664#Comment_16794664

What do you mean by chr(255) "does not work"?

It does work on my system (Oracle 12.2; my NLS_DATE_FORMAT is usually yyyy-mm-dd hh24:mi:ss, as it is in this example):

select to_date('31'||chr(255)||'Jan'||chr(255)||'2020', 'dd/mm/yyyy') as dt from dual;


DT                
-------------------
2020-01-31 00:00:00