How to solve ORA 01843 and NLS_DATE_FORMAT problem

The character type parameter '19-Novemon-08' is passed in the where clause of Oracle SQL so that it can be compared directly to the date type, or compared to the date type under conversion 1.
If the character format is passed in with NSL_DATE_FORMAT1, then it can be used without conversion, otherwise an ORA-01861 error will be reported. If you do not convert correctly, you may report ES9en-01843 or some other error.
Such as:
Trc code
SQL >

 
elect count(*) from dba_objects where created>to_date('2008-12-01'); 
select count(*) from dba_objects where created>to_date('2008-12-01') 

Error in line 1:
ORA-01861: The text does not match the format string
Trc code
SQL >

 
select count(*) from dba_objects where created>to_date('19-11 month -08','mm-dd- 
yyyy'); 
select count(*) from dba_objects where created>to_date('19-11 month -08','mm-dd-yyyy' 
) 

Error in line 1:
ORA-01843: Invalid month
This error occurs in unrecognized strings and in string tests where the format conversion is incorrect.
The format strings converted by to_date are mainly 'DD-ES36en-ES37en '/' DD-ES39en-ES40en' or 'YYYY-ES42en-ES43en '/' ES44en-ES45en-ES46en'.
If it can be identified, the correct result should look like this.
SQL >

 
select count(*) from dba_objects where created>'19-11 month -08'; 

COUNT(*)
----------
4199
This format is related to the value of the NLS_DATE_FORMAT parameter for the session.

 
select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL from dual 

DF DL
-------------------- --------------------
DD-MON-RR SIMPLIFIED CHINESE
We change the value of this parameter under 1 at the session level.
SQL > alter session set nls_date_format='YYYY-MM-DD';
The session has changed.
SQL >

 
select count(*) from dba_objects where created>'19-11 month -08'; 
select count(*) from dba_objects where created>'19-11 month -08' 

Error in line 1:
ORA-01861: The text does not match the format string
In this way, the original correct operation is not right. Use a string that matches the NLS_DATE_FORMAT format.
SQL >

 
select count(*) from dba_objects where created>'2008-12-01'; 

This parameter is also dependent on the environment of the application, and some applications will automatically modify this parameter value. Therefore, testing is best done in sqlplus.
The value of this parameter is fixed at the database level, as shown below:
Trc code
SQL >

 
select * from v$nls_parameters; 
PARAMETER VALUE 
------------------------------ ---------------------------------------- 
NLS_LANGUAGE SIMPLIFIED CHINESE 
NLS_TERRITORY CHINA 
NLS_CURRENCY  RMB  
NLS_ISO_CURRENCY CHINA 
NLS_NUMERIC_CHARACTERS ., 
NLS_CALENDAR GREGORIAN 
NLS_DATE_FORMAT DD-MON-RR 
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE 
NLS_CHARACTERSET ZHS16GBK 
NLS_SORT BINARY 
NLS_TIME_FORMAT HH.MI.SSXFF AM 
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 
NLS_DUAL_CURRENCY  RMB  
NLS_NCHAR_CHARACTERSET AL16UTF16 
NLS_COMP BINARY 
NLS_LENGTH_SEMANTICS BYTE 
NLS_NCHAR_CONV_EXCP FALSE 

19 rows have been selected.
NLS_DATE_LANGUAGE is "SIMPLIFIED CHINESE", simplified Chinese. So the monthly value is "November" in Chinese.