Analysis of ORA-01843 not a valid month when to_date is converted into a string

When developing Oracle SQL or PL/SQL programs, we often encounter the need to convert the string type to the DATE type. We will use to_date to implement this conversion requirement. But sometimes this conversion will go wrong and the ORA-01843: not a valid month is reported. Sometimes this type of conversion is normal during testing on the development system, but this error occurs when it is deployed to the production environment.

For example, we need to convert string '19-JUN-01 'to a time-type object.

This string is a common expression of time in the English environment, "day-month-Year", where "month" is abbreviated in English. Therefore, use to_date (xx, 'dd-mon-yy') to convert it. The conversion format is 'dd-mon-yy '.

However, in the actual conversion operation, the conversion fails as follows:

SQL> select To_Date ('19-JUN-01 ', 'dd-mon-yy') from dual;
Select To_Date ('19-JUN-01 ', 'dd-mon-yy') from dual
ORA-01843: not a valid month

The data corresponding to the error message "month" is invalid.

That is to say, the to_date function cannot recognize "JUN" in this system. This is short for the English month.

If it is changed to a Chinese expression, for example, '19-august 1-01 '. Then the system will be able to convert correctly. As follows:

SQL> select To_Date ('19-October-01', 'dd-mon-yy') from dual;
TO_DATE ('19-October-01', 'dd-MON-Y
------------------------------
2001-

The essence of this problem is that the system can recognize Chinese characters rather than Chinese characters.

The language configuration of the Oracle system is mainly stored in the V $ NLS_PARAMETERS data dictionary view. Query the language settings of the view. As follows:

SQL> select * from v $ nls_parameters where parameter like '% DATE % ';
 
PARAMETER VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE

It is confirmed that the database system is a simplified Chinese language environment, which is also a database instance-level configuration.

If you want To_Date ('19-JUN-01 ', 'dd-mon-yy') to operate normally, you only need to modify the value of NLS_DATE_LANGUAGE to AMERICAN to recognize it.

For developers, the instance-level language Setting Parameter NLS_DATE_LANGUAGE cannot be modified, but you can modify the session-level. You can change the value of this parameter to AMERICAN in the session of the database instance you connected.

SQL> alter session set nls_date_language = 'American ';
 
Session altered
 
SQL> select To_Date ('19-JUN-01 ', 'dd-mon-yy') from dual;
 
TO_DATE ('19-JUN-01 ', 'dd-MON-YY
------------------------------
2001-6-19
 
SQL>

In this way, you can use it.

1 2 Next Page