Analysis of to_date time conversion in Oracle multi-language environment

Symptom: Anyone who has used Oracle in a multi-language environment must have encountered such a problem,

Date_v date; date_v: = to_date (2010/11/16); -- different Oracle clinet may not work for the same server on 2010/11/16.

Cause: If the conversion string is not specified, Oracle uses the specified format string for date conversion and runs SELECT * FROM NLS_SESSION_PARAMETERS to view NLS information, here, NLS_DATE_FORMAT refers to the currently used formatted string. I am here "DD-MON-RR", dd represents the day rr represents the year mon represents the month, so to_date (2010/11/16) = to_date (2010/11/16, DD-MON-RR ),

When NLS_LANGUAGE is AMERICAN, MON only recognizes Nov instead of 11. Therefore, the conversion fails.

Solution: Since you know the cause of the problem, there are three solutions to this problem.

1. Changing NLS_LANGUAGE will synchronize NLS_DATE_FORMAT to the corresponding format. We need the japanese format here. I implemented this by adding an environment variable (NLS_LANG = JAPANESE_JAPAN.JA16SJIS.

NLS_DATE_FORMAT becomes RR-MM-DD, test, select to_date (2010/11/15) from dual -- OK.

2, add the environment variable NLS_DATE_FORMAT (RR-MM-DD or yy-mm-dd Or yy/mm/dd any format you need ).

Test: select to_date (2010/11/15) from dual -- OK.

Note that, after my test, rr and yy mean that the conversion can be successful by using the connection symbols-and.

To change the NLS_LANGUAGE or NLS_DATE_FORMAT, google.

This is basically done, but you may say that we cannot modify these configurations of the Oracle client as other instances may need them! The following is the third method.

3. to_date has an overload method that provides the format required for conversion.

Select to_date (2010/11/15, yy/mm/dd) from dual -- OK regardless of what NLS_DATE_FORMAT is.

But don't be happy too early. select to_date (2010/nov/15, yy/mon/dd) from dual -- not necessarily OK.

When the NLS_LANGUAGE environment is AMERICAN, it is OK. When it is East Asia language, the mon format character truly recognizes November. (here, the Japanese and china are still different. It looks like a word, however, an error occurred while inputting select to_date (2010/11 months/15, yy/mon/dd) from dual from my machine.

Specific details can refer to the article http://www.eygle.com/special/NLS_CHARACTER_SET_05.htm

Now the problem is clear, but there are so many solutions, is it true that the conversion of a string to the date is so twists and turns?

I chose the third method because it is controllable. Select to_date (2010/11/15, yy/mm/dd) from dual -- OK, because the translation of mm is always Arabic numerals.

Case: In this scenario, there is a storage to be deployed on an unknown geographic Unknown Server. It requires a date parameter, which is defined by plsql programmers as date. Unfortunately, another programmer who calls storage is a programmer who wants to call storage through a script and trigger script execution through a windows scheduled task. This script provides a script of 2010/11/15, which looks okay, unfortunately, the NLS_LANGUAGE of the Oracle client of this application server is AMERICAN. Unfortunately, it cannot be written. When Oracle performs the parameter conversion before such a call, date_v date; date_v: = 2010/11/16 (this conversion is uncontrollable by default); disaster is approaching.

How can we avoid such a disaster? Change the parameter to nvarchar and perform parameter conversion by yourself. Use the preceding third solution data_v: = to_date (data_vchar, yy/mm/dd) to avoid the disaster.

Some people will say that the person who calls the storage has sent a 2010 RMB/11 ...... /16! What should I do? What should I do with your yyyy/mm/dd? Of course it cannot be processed. My method is to provide an accurate document to tell the other party what the parameter format should be. If something goes wrong, you can also eliminate the responsibility. In addition, I can add some exception mechanisms to prompt for the problem. Oracle's automatic conversion error is very aggressive.