To_date time conversion problem in Oracle Multi-language environment

phenomenon : Students who have used Oracle in a multi-lingual environment must have encountered such a problem,

Date_v date;

Date_v: = to_date (' 2010/11/16 ');--or ' 2010/11/16 '

The same server, different Oracle clinet does not necessarily work.

cause : Without specifying a conversion string, Oracle uses the established format string for date conversion operations, executes select * from Nls_session_parameters, and sees NLS information, where Nls_date_ Format is the formatted string for the current formatted date. I am Here "dd-mon-rr" (DD represents the day RR represents the year MON represents the month), so the default conversion becomes to_date (' 2010/11/16 ', ' dd-mon-rr '). Mon in the case of nls_language for American only to recognize the 11, so the conversion failed.

Solution : Now that you know the cause of the problem, there are three ways to solve the problem.

1, changing the nls_language will synchronize the Nls_date_format to the appropriate format. We need Japanese format here. I do this by adding environment variables (nls_lang=japanese_japan. Ja16sjis) implementation.

Nls_date_format became RR-MM-DD, tested, 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.

Here to note after I test that RR and yy are a meaning, the connection symbol-and/both can make the conversion successful.

The way to change nls_language or Nls_date_format can be under Google.

It's almost done, but you might say we can't just modify these configurations for Oracle client, because other instances might need it! Here's a third method

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

Whatever Nls_date_format is, select To_date (' 2010/11/15 ', ' yy/mm/dd ') from Dual--ok.

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 for American is OK, for East Asia language the Mon format character really recognizes November (here Japan and China are not the same, looks like a word, but from my machine input select To_ Date (' 2010/11 months/15 ', ' YY/MON/DD ') from dual but error.

For details, refer to article http://www.eygle.com/special/NLS_CHARACTER_SET_05.htm

Now that the problem is clear, but the solution is so much, is it really so difficult to convert a string to a date?

I choose the third method, because it is what I can control. Select To_date (' 2010/11/15 ', ' yy/mm/dd ') from Dual--ok, because the translation of MM is always Arabic numerals ah.

4, as a SQL function parameter. NLS parameters can be used explicitly to hardcode NLS behavior within a SQL function. Doing so would override the defaults set for the session on the initialization file, the client with environment variables, or set for the session by ALTER session. For example:

To_char (' Mar ', ' MM  dd yyyy ', ' nls_date_language = AMERICAN ')

5,as initialization parameters on the server. You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings has no effect on the client side; They control only the server ' s behavior. For example:

Nls_territory = "CZECH REPUBLIC"

6,as ALTER SESSION parameters. NLS parameters set in an ALTER session statement can is used to override the defaults set for the session in the Initializ ation file, or set by the client with environment variables.

sql> ALTER SESSION SET nls_sort = FRENCH;

There are several additional methods that have recently encountered a problem with a CSV from Sybase using SQLLDR, a column of time is Mar 28 2011, which, if you want to import into the date column, is only set to this in the column configuration of the control file:

。。。

, Record_date CHAR "to_date (: record_date, ' MM dd yyyy ', ' Nls_date_language=american ')"

。。。

The final import is successful, and the principle is "tell to sqlloader that I need to the change his session".

This link has Oracle knowledge about NLS: http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76966/ch2.htm#91066

Here are some advanced information about the SQLLDR load file: http://www.cnblogs.com/wzc998/archive/2011/03/24/1994137.html

case : In this scenario, there is now a storage to be deployed on unknown geographic unknown server, it needs a date parameter, Plsql programmer take it for granted it is defined as date, unfortunately, another programmer that calls the store is a script that wants to call the store, And with the Windows Scheduled task trigger script execution, this script gave a 2010/11/15, it seems no problem, but unfortunately this application server Oracle client Nls_language=american, really Wuqiaobuchengshu Ah, Date_v Date when Oracle makes the parameter conversion work before such a call; Date_v: = ' 2010/11/16 ' (this conversion is not controlled by default); The disaster has come.

How to avoid such a disaster? Change the parameters to nvarchar bar, do their own parameter conversion, using the above our third solution data_v:= to_date (Data_vchar, ' yy/mm/dd '), in fact, disaster can be avoided.

Someone would say that the person who called the store passed a 2010￥/11....../16! What do you do with your YYYY/MM/DD? Of course, my method is to give an accurate document to tell the other parameters should be what format, error can also exclude responsibility. In addition to my own conversion can add some abnormal mechanism to prompt the problem, Oracle automatic conversion error but very overbearing.

what the NLS is , can see here http://www.cnblogs.com/wzc998/archive/2010/11/17/1879652.html

To_date time conversion problem in Oracle Multi-language environment