How to solve ORA-01843 and NLS_DATE_FORMAT Problems

Source: Internet
Author: User

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 ".

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.