[SOLVED] Changing my oracle NLS

Changing my oracle NLS_Lang environment

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?

Solution

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')