Globalization

NLS_TERRITORY to NLS_DATE_FORMAT Mapping Table

How NLS_LANG Affects NLS_DATE_FORMAT When NLS_LANG is Set Territory Only

NLS_TERRITORY to NLS_DATE_FORMAT

Here in this post is the mapping table for showing NLS_TERRITORY to their respective NLS_DATE_FORMAT. In some situations, you may need this mapping table to solve globalization problems. For instances, ORA-01861 and ORA-12705.

Please note that, NLS_DATE_FORMAT is not derived from NLS_LANGUAGE, in fact, it’s derived from NLS_TERRITORY. Therefore, any conflicts between NLS_TERRITORY and NLS_LANGUAGE, the former one suppresses the later one.

Let’s do a small test, then you’ll know the truth. First of all, I set NLS_LANGUAGE to SWEDISH via setting NLS_LANG environment variable.

[oracle@test ~]$ export NLS_LANG=SWEDISH
[oracle@test ~]$ echo $NLS_LANG
SWEDISH

Then I checked related NLS settings.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> column parameter format a20;
SQL> column value format a20;
SQL> select parameter, value from v$nls_parameters where parameter in ('NLS_TERRITORY', 'NLS_LANGUAGE', 'NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT');

PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         SWEDISH
NLS_TERRITORY        SWEDEN
NLS_DATE_FORMAT      RRRR-MM-DD
NLS_DATE_LANGUAGE    SWEDISH

I made a change on NLS_TERRITORY into JAPAN.

SQL> alter session set nls_territory="JAPAN";

Session ▒r ▒ndrad.

Let’s see the result.

SQL> select parameter, value from v$nls_parameters where parameter in ('NLS_TERRITORY', 'NLS_LANGUAGE', 'NLS_DATE_LANGUAGE', 'NLS_DATE_FORMAT');

PARAMETER            VALUE
-------------------- --------------------
NLS_LANGUAGE         SWEDISH
NLS_TERRITORY        JAPAN
NLS_DATE_FORMAT      RR-MM-DD
NLS_DATE_LANGUAGE    SWEDISH

The NLS_TERRITORY had been changed into JAPAN and NLS_DATE_FORMAT was changed too. There’s nothing to do with NLS_LANGUAGE.

Moreover, NLS_TERRITORY controls several formats and conventions, which include:

  • Date format
  • Decimal character and group separator
  • Local currency symbol
  • ISO currency symbol
  • Dual currency symbol
  • First day of the week
  • Credit and debit symbols
  • ISO week flag
  • List separator

Mapping Table

NLS_TERRITORYNLS_DATE_FORMAT
AFGHANISTANDD/MM/RR
ALBANIADD.MM.RRRR
ALGERIADD-MM-RR
AMERICADD-MON-RR
ANGOLADD/MM/RR
ANTIGUA AND BARBUDADD/MM/RR
ARGENTINADD/MM/RRRR
ARMENIADD.fmMM.RRRR
ARUBADD-MM-RR
AUSTRALIADD/MON/RR
AUSTRIADD.MM.RR
AZERBAIJANDD.MM.RRRR
BAHAMASDD/MM/RRRR
BAHRAINDD-MM-RR
BANGLADESHDD-MM-RRRR
BARBADOSDD/MM/RR
BELARUSDD.MM.RR
BELGIUMDD/MM/RR
BELIZEDD/MM/RR
BERMUDADD/MM/RRRR
BOLIVIAfmDD-MM-RR
BOSNIA AND HERZEGOVINAfmMM.DD.RRRR
BOTSWANADD/MM/RR
BRAZILDD/MM/RR
BULGARIARRRR-MM-DD
CAMBODIARRRR-fmMM-DD
CAMEROONDD/MM/RR
CANADARR-MM-DD
CATALONIADD/MM/RR
CAYMAN ISLANDSDD/MM/RR
CHILEDD/MM/RR
CHINADD-MON-RR
COLOMBIADD/MM/RR
CONGO BRAZZAVILLEDD/MM/RR
CONGO KINSHASADD/MM/RR
COSTA RICADD/MM/RR
CROATIADD.MM.RR
CURACAODD-MM-RR
CYPRUSDD/MM/RR
CZECH REPUBLICDD.MM.RR
DENMARKRR-MM-DD
DJIBOUTIDD/MM/RR
DOMINICADD/MM/RR
DOMINICAN REPUBLICfmDD/MM/fmRR
ECUADORDD/MM/RRRR
EGYPTDD/MM/RR
EL SALVADORDD/MM/RR
ESTONIADD.MM.RRRR
ETHIOPIAfmDD/MM/RRRR
FINLANDDD.MM.RRRR
FRANCEDD/MM/RR
FYR MACEDONIADD.MM.RRRR
GABONDD/MM/RR
GERMANYDD.MM.RR
GHANADD/MM/RR
GREECEDD/MM/RR
GRENADADD/MM/RR
GUATEMALADD/MM/RR
GUYANADD/MM/RR
HAITIDD/MM/RR
HONDURASDD/MM/RR
HONG KONGDD-MON-RR
HUNGARYRR-MON-DD
ICELANDDD.MM.RRRR
INDIADD-MM-RR
INDONESIADD-MM-RRRR
IRANRRRR/fmMM/fmDD
IRAQDD/MM/RR
IRELANDDD-MON-RR
ISRAELDD-MON-RRRR
ITALYDD-MON-RR
IVORY COASTDD/MM/RR
JAMAICAfmDD/MM/fmRR
JAPANRR-MM-DD
JORDANDD/MM/RR
KAZAKHSTANDD.MM.RRRR
KENYAfmMM/DD/RRRR
KOREARR/MM/DD
KUWAITDD/MM/RR
LAOSDD/fmMM/RRRR
LATVIARRRR.MM.DD
LEBANONDD/MM/RR
LIBYADD/MM/RR
LIECHTENSTEINDD.MM.RR
LITHUANIARRRR.MM.DD
LUXEMBOURGDD/MM/RR
MACAOfmDD/MM/fmRR
MALAWIDD/MM/RR
MALAYSIADD/MM/RRRR
MALDIVESfmDD/fmMM/RR
MALTAfmDD/MM/RRRR
MAURITANIADD/MM/RR
MAURITIUSDD/MM/RR
MEXICODD/MM/RR
MOLDOVADD.MM.RR
MONTENEGRODD.MM.RRRR.
MOROCCODD-MM-RR
MOZAMBIQUEDD/MM/RR
NAMIBIAfmDD/MM/fmRR
NEPALfmDD/MM/RRRR
NEW ZEALANDDD/MM/RR
NICARAGUADD/MM/RR
NIGERIADD/fmMM/RRRR
NORWAYDD.MM.RRRR
OMANDD/MM/RR
PAKISTANDD-MON-RR
PANAMADD/MM/RR
PARAGUAYfmDD/MM/RR
PERUDD/MM/RR
PHILIPPINESMM/DD/RRRR
POLANDRR/MM/DD
PORTUGALRR.MM.DD
PUERTO RICODD/MM/RR
QATARDD/MM/RR
ROMANIADD-MM-RRRR
RUSSIADD.MM.RR
SAINT KITTS AND NEVISDD/MM/RR
SAINT LUCIADD/MM/RR
SAUDI ARABIADD/MM/RR
SENEGALDD/MM/RRRR
SERBIADD.MM.RRRR.
SIERRA LEONEDD/MM/RR
SINGAPOREDD/MM/RR
SLOVAKIADD.MM.RR
SLOVENIADD.MM.RR
SOMALIADD/MM/RR
SOUTH AFRICADD/MON/RR
SOUTH SUDANDD/MM/RR
SPAINDD/MM/RR
SRI LANKARRRR/fmMM/fmDD
SUDANDD/MM/RR
SURINAMEDD-MM-RR
SWAZILANDDD/MM/RR
SWEDENRRRR-MM-DD
SWITZERLANDDD.MM.RR
SYRIADD/MM/RR
TAIWANDD-MON-RR
TANZANIAfmMM/DD/RRRR
THAILANDDD MON RRRR
THE NETHERLANDSDD-MM-RR
TRINIDAD AND TOBAGODD/MM/RR
TUNISIADD-MM-RR
TURKEYDD/MM/RRRR
TURKMENISTANDD.MM.RR
UGANDADD/MM/RRRR
UKRAINEDD.MM.RRRR
UNITED ARAB EMIRATESDD/MM/RR
UNITED KINGDOMDD-MON-RR
URUGUAYDD/MM/RR
UZBEKISTANDD/MON/RR
VENEZUELADD/MM/RR
VIETNAMDD-MM-RRRR
YEMENDD/MM/RR
ZAMBIADD/MM/RRRR
ZIMBABWEfmDD/MM/fmRR

For more explanations on format elements, please refer to Oracle 19c SQL Language Reference: Datetime Format Models.

Leave a Reply

Your email address will not be published. Required fields are marked *