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