Changing my oracle NLS_Lang environment

Question

I need to format a string ot date including abbreviated strings. I tried with:

to_date('Fri  Jul 24', 'DY MON DD')

It doesn't work, I figured it was because my oracle NLS parameters are set to French I have no idea why:

SELECT * FROM V$NLS_PARAMETERS
----------------------
NLS_LANGUAGE                FRENCH
NLS_TERRITORY               FRANCE
NLS_CURRENCY                €
NLS_ISO_CURRENCY            FRANCE
NLS_NUMERIC_CHARACTERS      , 
NLS_CALENDAR                GREGORIAN
NLS_DATE_FORMAT             DD/MM/RR
NLS_DATE_LANGUAGE           FRENCH
NLS_CHARACTERSET            AL32UTF8
NLS_SORT                    FRENCH
NLS_TIME_FORMAT             HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT        DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT          HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT     DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY           €
NLS_NCHAR_CHARACTERSET      AL16UTF16
NLS_COMP                    BINARY
NLS_LENGTH_SEMANTICS        BYTE
NLS_NCHAR_CONV_EXCP         FALSE

So of course I tested the french conversion:

to_date('Vendredi  Juillet 24', 'DAY MONTH DD')

This works. Then I tried the abbreviated version:

to_date('Ven  Jui 24', 'DY MON DD')

which does NOT work.

Anyway, I ma interested in the English conversion, so I should start by switching my NLS from French to English. How do I do that? I followed those steps from this link: http://docs.oracle.com/cd/E12102_01/books/AnyInstAdm784/AnyInstAdmPreInstall18.html, but without any success. Any idea?

The abbreviated version didn't work, because you used wrong month name - `Jui` instead of `Juil` (missing `l`). Anyway, you don't have to change the server parameters, you can use NLSPARAM to force desired conversion, just: `to_date('Fri Jul 24', 'DY MON DD', 'NLS_DATE_LANGUAGE = American' )`. You can also change it for your session: `ALTER SESSION SET NLS_DATE_LANGUAGE = 'American'` and use `to_date` without nls parameter. — krokodilko, Jul 24 '15 at 16:27
Thank you very much! Ii couldn't understand how to set the nls language from techonthenet.com :) — kaligne, Jul 25 '15 at 08:16
See my answer, I hope that it might be usefull in uderstanding how NLS parameters work on Oracle. — krokodilko, Jul 25 '15 at 08:49

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Refer rather to this link if you want to change - and first understand how they work - NLS parameters: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1005949

NLS parameters can be specified in the following ways:

As initialization parameters on the server

You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:
                  NLS_TERRITORY = "CZECH REPUBLIC"

2. As environment variables on the client

You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:
                  % setenv NLS_SORT FRENCH

3. With the ALTER SESSION statement

You can use NLS parameters that are set in an ALTER SESSION statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
                ALTER SESSION SET NLS_SORT = FRENCH;

4. In SQL functions

You can use NLS parameters explicitly to hardcode NLS behavior within a SQL function. This practice overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement. For example:
         TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Notice that the session NLS parameters on the client (not servers' parameters) decide how conversions work in your SQL statements. The client can derive it's own NLS parameters from the server, but it doesn't have to do it. For example in SQL Developer you can set NLS parameters of your session in the menu under Tools/Preferences/Database/NLS option, but default settings are usually derived from your PC (for example from MS-Windows language settings or NLS_LANG environment variable, if exists).
Each client has it's own specific method to set these parameters, you must refer to documentaton of your client.

It makes sense, because for example imagine that the server is located in France and has NLS settings = 'France', but the first client is connecting from America, and the second one from Norway - the first client want to see American dates, so it sets NLS parameters on it's client to NLS = 'America. The second client sets it's language settings to NLS = 'Norway" an it sees dates in local approtirate format.

You can always change NLS settings in your session, regardless of the client, using ALTER SESSION command.

score 1 · Accepted Answer · answered Jul 24 '15 at 18:14

The abbreviated French form is Ven. Juil. 24 the periods appear to be integral.

You can change the date language of the TO_DATE and TO_CHAR functions by supplying the optional 3rd argument e.g.:

to_char(sysdate,'Dy Mon dd','NLS_DATE_LANGUAGE = FRENCH')
to_char(sysdate,'Dy Mon dd','NLS_DATE_LANGUAGE = AMERICAN')

Changing my oracle NLS_Lang environment

2 Answers2