How to solve ORA-01843 and NLS_DATE_FORMAT Problems

In the where clause of Oracle SQL, input the character type parameter '19th-August 08 ', so that it can be directly compared with the date type or converted to the same date type comparison.
If the entered character format is the same as NSL_DATE_FORMAT, you can directly use it without conversion; otherwise, a ORA-01861 error is reported. If the conversion is incorrect, A ORA-01843 or other error may be reported.
For example:
Trc code
SQL>
Copy codeThe Code is as follows:
Elect count (*) from dba_objects where created> to_date ('2017-12-01 ');
Select count (*) from dba_objects where created> to_date ('2017-12-01 ')

Row 3 has an error:
ORA-01861: Text and format strings do not match
Trc code
SQL>
Copy codeThe Code is as follows:
Select count (*) from dba_objects where created> to_date ('19-January 1, November-08 ', 'Mm-dd-
Yyyy ');
Select count (*) from dba_objects where created> to_date ('19-January 1, November-08', 'Mm-dd-yyyy'
)

Row 3 has an error:
ORA-01843: Invalid month
This error occurs when testing strings that cannot be recognized and strings whose formats are not converted correctly.
Format strings converted using to_date are primarily 'dd-MON-RR '/'dd-MON-RRRR' or 'yyyy-MM-DD '/'yy-MM-DD '.
If it can be identified, the correct result should be like this.
SQL>
Copy codeThe Code is as follows:
Select count (*) from dba_objects where created> '19-August 8-08 ';

COUNT (*)
----------
4199
This format is related to the value of the NLS_DATE_FORMAT parameter of the session.
Copy codeThe Code is as follows:
Select SYS_CONTEXT ('userenv', 'nls _ DATE_FORMAT ') DF, SYS_CONTEXT ('userenv', 'nls _ DATE_LANGUAGE') DL from dual

DF DL
----------------------------------------
DD-MON-RR SIMPLIFIED (CHINESE)
Modify the value of this parameter at the session level.
SQL> alter session set nls_date_format = 'yyyy-MM-DD ';
The session has been changed.
SQL>
Copy codeThe Code is as follows:
Select count (*) from dba_objects where created> '19-August 8-08 ';
Select count (*) from dba_objects where created> '19-August 8-08'

Row 3 has an error:
ORA-01861: Text and format strings do not match
In this way, the correct operation is incorrect. Use a string that complies with the NLS_DATE_FORMAT format.
SQL>
Copy codeThe Code is as follows:
Select count (*) from dba_objects where created> '2017-12-01 ';

This parameter is also related to the application environment, and some applications will automatically modify this parameter value. Therefore, it is best to put the test in sqlplus.
The value of this parameter remains unchanged at the database level, as shown below:
Trc code
SQL>
Copy codeThe Code is as follows:
Select * from v $ nls_parameters;
PARAMETER VALUE
----------------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY $
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 $
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", that is, simplified chinese. Therefore, the month value is in Chinese format, for example, "January 1, November ".