NLS_LANGUAGE to NLS_DATE_FORMAT
Here in this post is the mapping table for showing NLS_LANGUAGE 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.
In order to change NLS_DATE_FORMAT, you can change NLS_LANGUAGE by setting the environment variable NLS_LANG at OS-level, then NLS_TERRITORY will follow the new NLS_LANGUAGE. Thereafter, all NLS (National Language Support) formats including NLS_DATE_FORMAT follows NLS_TERRITORY. That is to say, NLS_TERRITORY is the real factor to determine all regional conventions.
Therefore, ALTER SESSION SET NLS_LANGUAGE or ALTER SESSION SET NLS_DATE_LANGUAGE does not affect NLS_DATE_FORMAT, but ALTER SESSION SET NLS_TERRITORY does. That's why you should additionally know NLS_TERRITORY to NLS_DATE_FORMAT Mapping Table.
Mapping Table
| NLS_LANGUAGE | NLS_DATE_FORMAT |
|---|---|
| ALBANIAN | DD.MM.RRRR |
| AMERICAN | DD-MON-RR |
| AMHARIC | fmDD/MM/RRRR |
| ARABIC | DD/MM/RR |
| ARMENIAN | DD.fmMM.RRRR |
| ASSAMESE | DD-MM-RR |
| AZERBAIJANI | DD.MM.RRRR |
| BANGLA | DD-MM-RR |
| BASQUE | DD/MM/RR |
| BELARUSIAN | DD.MM.RR |
| BRAZILIAN PORTUGUESE | DD/MM/RR |
| BULGARIAN | RRRR-MM-DD |
| CANADIAN FRENCH | RR-MM-DD |
| CATALAN | DD/MM/RR |
| CROATIAN | DD.MM.RR |
| CYRILLIC KAZAKH | DD.MM.RRRR |
| CYRILLIC SERBIAN | DD.MM.RRRR. |
| CYRILLIC UZBEK | DD/MON/RR |
| CZECH | DD.MM.RR |
| DANISH | RR-MM-DD |
| DARI | DD/MM/RR |
| DIVEHI | fmDD/fmMM/RR |
| DUTCH | DD-MM-RR |
| EGYPTIAN | DD/MM/RR |
| ENGLISH | DD-MON-RR |
| ESTONIAN | DD.MM.RRRR |
| FINNISH | DD.MM.RRRR |
| FRENCH | DD/MM/RR |
| GERMAN | DD.MM.RR |
| GERMAN DIN | DD.MM.RR |
| GREEK | DD/MM/RR |
| GUJARATI | DD-MM-RR |
| HEBREW | DD-MON-RRRR |
| HINDI | DD-MM-RR |
| HUNGARIAN | RR-MON-DD |
| ICELANDIC | DD.MM.RRRR |
| INDONESIAN | DD-MM-RRRR |
| IRISH | DD-MON-RR |
| ITALIAN | DD-MON-RR |
| JAPANESE | RR-MM-DD |
| KANNADA | DD-MM-RR |
| KHMER | RRRR-fmMM-DD |
| KOREAN | RR/MM/DD |
| LAO | DD/fmMM/RRRR |
| LATIN AMERICAN SPANISH | DD-MON-RR |
| LATIN BOSNIAN | fmMM.DD.RRRR |
| LATIN SERBIAN | DD.MM.RRRR. |
| LATIN UZBEK | DD/MON/RR |
| LATVIAN | RRRR.MM.DD |
| LITHUANIAN | RRRR.MM.DD |
| MACEDONIAN | DD.MM.RRRR |
| MALAY | DD/MM/RRRR |
| MALAYALAM | DD-MM-RR |
| MALTESE | fmDD/MM/RRRR |
| MARATHI | DD-MM-RR |
| MEXICAN SPANISH | DD/MM/RR |
| NEPALI | fmDD/MM/RRRR |
| NORWEGIAN | DD.MM.RRRR |
| ORIYA | DD-MM-RR |
| PERSIAN | RRRR/fmMM/fmDD |
| POLISH | RR/MM/DD |
| PORTUGUESE | RR.MM.DD |
| PUNJABI | DD-MM-RR |
| ROMANIAN | DD-MM-RRRR |
| RUSSIAN | DD.MM.RR |
| SIMPLIFIED CHINESE | DD-MON-RR |
| SINHALA | RRRR/fmMM/fmDD |
| SLOVAK | DD.MM.RR |
| SLOVENIAN | DD.MM.RR |
| SPANISH | DD/MM/RR |
| SWAHILI | fmMM/DD/RRRR |
| SWEDISH | RRRR-MM-DD |
| TAMIL | DD-MM-RR |
| TELUGU | DD-MM-RR |
| THAI | DD MON RRRR |
| TRADITIONAL CHINESE | DD-MON-RR |
| TURKISH | DD/MM/RRRR |
| TURKMEN | DD.MM.RR |
| UKRAINIAN | DD.MM.RRRR |
| URDU | DD-MON-RR |
| VIETNAMESE | DD-MM-RRRR |
For more explanations on format elements, please refer to Oracle 19c SQL Language Reference: Datetime Format Models.