Globalization

NLS_LANGUAGE to NLS_DATE_FORMAT Mapping Table

How NLS_LANG Affects NLS_DATE_FORMAT When NLS_LANG is Set Language Only

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_LANGUAGENLS_DATE_FORMAT
ALBANIANDD.MM.RRRR
AMERICANDD-MON-RR
AMHARICfmDD/MM/RRRR
ARABICDD/MM/RR
ARMENIANDD.fmMM.RRRR
ASSAMESEDD-MM-RR
AZERBAIJANIDD.MM.RRRR
BANGLADD-MM-RR
BASQUEDD/MM/RR
BELARUSIANDD.MM.RR
BRAZILIAN PORTUGUESEDD/MM/RR
BULGARIANRRRR-MM-DD
CANADIAN FRENCHRR-MM-DD
CATALANDD/MM/RR
CROATIANDD.MM.RR
CYRILLIC KAZAKHDD.MM.RRRR
CYRILLIC SERBIANDD.MM.RRRR.
CYRILLIC UZBEKDD/MON/RR
CZECHDD.MM.RR
DANISHRR-MM-DD
DARIDD/MM/RR
DIVEHIfmDD/fmMM/RR
DUTCHDD-MM-RR
EGYPTIANDD/MM/RR
ENGLISHDD-MON-RR
ESTONIANDD.MM.RRRR
FINNISHDD.MM.RRRR
FRENCHDD/MM/RR
GERMANDD.MM.RR
GERMAN DINDD.MM.RR
GREEKDD/MM/RR
GUJARATIDD-MM-RR
HEBREWDD-MON-RRRR
HINDIDD-MM-RR
HUNGARIANRR-MON-DD
ICELANDICDD.MM.RRRR
INDONESIANDD-MM-RRRR
IRISHDD-MON-RR
ITALIANDD-MON-RR
JAPANESERR-MM-DD
KANNADADD-MM-RR
KHMERRRRR-fmMM-DD
KOREANRR/MM/DD
LAODD/fmMM/RRRR
LATIN AMERICAN SPANISHDD-MON-RR
LATIN BOSNIANfmMM.DD.RRRR
LATIN SERBIANDD.MM.RRRR.
LATIN UZBEKDD/MON/RR
LATVIANRRRR.MM.DD
LITHUANIANRRRR.MM.DD
MACEDONIANDD.MM.RRRR
MALAYDD/MM/RRRR
MALAYALAMDD-MM-RR
MALTESEfmDD/MM/RRRR
MARATHIDD-MM-RR
MEXICAN SPANISHDD/MM/RR
NEPALIfmDD/MM/RRRR
NORWEGIANDD.MM.RRRR
ORIYADD-MM-RR
PERSIANRRRR/fmMM/fmDD
POLISHRR/MM/DD
PORTUGUESERR.MM.DD
PUNJABIDD-MM-RR
ROMANIANDD-MM-RRRR
RUSSIANDD.MM.RR
SIMPLIFIED CHINESEDD-MON-RR
SINHALARRRR/fmMM/fmDD
SLOVAKDD.MM.RR
SLOVENIANDD.MM.RR
SPANISHDD/MM/RR
SWAHILIfmMM/DD/RRRR
SWEDISHRRRR-MM-DD
TAMILDD-MM-RR
TELUGUDD-MM-RR
THAIDD MON RRRR
TRADITIONAL CHINESEDD-MON-RR
TURKISHDD/MM/RRRR
TURKMENDD.MM.RR
UKRAINIANDD.MM.RRRR
URDUDD-MON-RR
VIETNAMESEDD-MM-RRRR

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 *