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_TERRITORY | NLS_DATE_FORMAT |
|---|---|
| AFGHANISTAN | DD/MM/RR |
| ALBANIA | DD.MM.RRRR |
| ALGERIA | DD-MM-RR |
| AMERICA | DD-MON-RR |
| ANGOLA | DD/MM/RR |
| ANTIGUA AND BARBUDA | DD/MM/RR |
| ARGENTINA | DD/MM/RRRR |
| ARMENIA | DD.fmMM.RRRR |
| ARUBA | DD-MM-RR |
| AUSTRALIA | DD/MON/RR |
| AUSTRIA | DD.MM.RR |
| AZERBAIJAN | DD.MM.RRRR |
| BAHAMAS | DD/MM/RRRR |
| BAHRAIN | DD-MM-RR |
| BANGLADESH | DD-MM-RRRR |
| BARBADOS | DD/MM/RR |
| BELARUS | DD.MM.RR |
| BELGIUM | DD/MM/RR |
| BELIZE | DD/MM/RR |
| BERMUDA | DD/MM/RRRR |
| BOLIVIA | fmDD-MM-RR |
| BOSNIA AND HERZEGOVINA | fmMM.DD.RRRR |
| BOTSWANA | DD/MM/RR |
| BRAZIL | DD/MM/RR |
| BULGARIA | RRRR-MM-DD |
| CAMBODIA | RRRR-fmMM-DD |
| CAMEROON | DD/MM/RR |
| CANADA | RR-MM-DD |
| CATALONIA | DD/MM/RR |
| CAYMAN ISLANDS | DD/MM/RR |
| CHILE | DD/MM/RR |
| CHINA | DD-MON-RR |
| COLOMBIA | DD/MM/RR |
| CONGO BRAZZAVILLE | DD/MM/RR |
| CONGO KINSHASA | DD/MM/RR |
| COSTA RICA | DD/MM/RR |
| CROATIA | DD.MM.RR |
| CURACAO | DD-MM-RR |
| CYPRUS | DD/MM/RR |
| CZECH REPUBLIC | DD.MM.RR |
| DENMARK | RR-MM-DD |
| DJIBOUTI | DD/MM/RR |
| DOMINICA | DD/MM/RR |
| DOMINICAN REPUBLIC | fmDD/MM/fmRR |
| ECUADOR | DD/MM/RRRR |
| EGYPT | DD/MM/RR |
| EL SALVADOR | DD/MM/RR |
| ESTONIA | DD.MM.RRRR |
| ETHIOPIA | fmDD/MM/RRRR |
| FINLAND | DD.MM.RRRR |
| FRANCE | DD/MM/RR |
| FYR MACEDONIA | DD.MM.RRRR |
| GABON | DD/MM/RR |
| GERMANY | DD.MM.RR |
| GHANA | DD/MM/RR |
| GREECE | DD/MM/RR |
| GRENADA | DD/MM/RR |
| GUATEMALA | DD/MM/RR |
| GUYANA | DD/MM/RR |
| HAITI | DD/MM/RR |
| HONDURAS | DD/MM/RR |
| HONG KONG | DD-MON-RR |
| HUNGARY | RR-MON-DD |
| ICELAND | DD.MM.RRRR |
| INDIA | DD-MM-RR |
| INDONESIA | DD-MM-RRRR |
| IRAN | RRRR/fmMM/fmDD |
| IRAQ | DD/MM/RR |
| IRELAND | DD-MON-RR |
| ISRAEL | DD-MON-RRRR |
| ITALY | DD-MON-RR |
| IVORY COAST | DD/MM/RR |
| JAMAICA | fmDD/MM/fmRR |
| JAPAN | RR-MM-DD |
| JORDAN | DD/MM/RR |
| KAZAKHSTAN | DD.MM.RRRR |
| KENYA | fmMM/DD/RRRR |
| KOREA | RR/MM/DD |
| KUWAIT | DD/MM/RR |
| LAOS | DD/fmMM/RRRR |
| LATVIA | RRRR.MM.DD |
| LEBANON | DD/MM/RR |
| LIBYA | DD/MM/RR |
| LIECHTENSTEIN | DD.MM.RR |
| LITHUANIA | RRRR.MM.DD |
| LUXEMBOURG | DD/MM/RR |
| MACAO | fmDD/MM/fmRR |
| MALAWI | DD/MM/RR |
| MALAYSIA | DD/MM/RRRR |
| MALDIVES | fmDD/fmMM/RR |
| MALTA | fmDD/MM/RRRR |
| MAURITANIA | DD/MM/RR |
| MAURITIUS | DD/MM/RR |
| MEXICO | DD/MM/RR |
| MOLDOVA | DD.MM.RR |
| MONTENEGRO | DD.MM.RRRR. |
| MOROCCO | DD-MM-RR |
| MOZAMBIQUE | DD/MM/RR |
| NAMIBIA | fmDD/MM/fmRR |
| NEPAL | fmDD/MM/RRRR |
| NEW ZEALAND | DD/MM/RR |
| NICARAGUA | DD/MM/RR |
| NIGERIA | DD/fmMM/RRRR |
| NORWAY | DD.MM.RRRR |
| OMAN | DD/MM/RR |
| PAKISTAN | DD-MON-RR |
| PANAMA | DD/MM/RR |
| PARAGUAY | fmDD/MM/RR |
| PERU | DD/MM/RR |
| PHILIPPINES | MM/DD/RRRR |
| POLAND | RR/MM/DD |
| PORTUGAL | RR.MM.DD |
| PUERTO RICO | DD/MM/RR |
| QATAR | DD/MM/RR |
| ROMANIA | DD-MM-RRRR |
| RUSSIA | DD.MM.RR |
| SAINT KITTS AND NEVIS | DD/MM/RR |
| SAINT LUCIA | DD/MM/RR |
| SAUDI ARABIA | DD/MM/RR |
| SENEGAL | DD/MM/RRRR |
| SERBIA | DD.MM.RRRR. |
| SIERRA LEONE | DD/MM/RR |
| SINGAPORE | DD/MM/RR |
| SLOVAKIA | DD.MM.RR |
| SLOVENIA | DD.MM.RR |
| SOMALIA | DD/MM/RR |
| SOUTH AFRICA | DD/MON/RR |
| SOUTH SUDAN | DD/MM/RR |
| SPAIN | DD/MM/RR |
| SRI LANKA | RRRR/fmMM/fmDD |
| SUDAN | DD/MM/RR |
| SURINAME | DD-MM-RR |
| SWAZILAND | DD/MM/RR |
| SWEDEN | RRRR-MM-DD |
| SWITZERLAND | DD.MM.RR |
| SYRIA | DD/MM/RR |
| TAIWAN | DD-MON-RR |
| TANZANIA | fmMM/DD/RRRR |
| THAILAND | DD MON RRRR |
| THE NETHERLANDS | DD-MM-RR |
| TRINIDAD AND TOBAGO | DD/MM/RR |
| TUNISIA | DD-MM-RR |
| TURKEY | DD/MM/RRRR |
| TURKMENISTAN | DD.MM.RR |
| UGANDA | DD/MM/RRRR |
| UKRAINE | DD.MM.RRRR |
| UNITED ARAB EMIRATES | DD/MM/RR |
| UNITED KINGDOM | DD-MON-RR |
| URUGUAY | DD/MM/RR |
| UZBEKISTAN | DD/MON/RR |
| VENEZUELA | DD/MM/RR |
| VIETNAM | DD-MM-RRRR |
| YEMEN | DD/MM/RR |
| ZAMBIA | DD/MM/RRRR |
| ZIMBABWE | fmDD/MM/fmRR |
For more explanations on format elements, please refer to Oracle 19c SQL Language Reference: Datetime Format Models.