Trying to write some information about the DATE datatype, I ran into the following problem:
SQL> insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC')); insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC')) * ERROR at line 1: ORA-01856: BC/B.C. or AD/A.D. required
Also different varieties (B.C. , bc, AD, A.D.) with or without a space, with different seperators all gave the same ORA-1856: BC/B.C. or AD/A.D. required.
Excuse me. That is what I just entered. What is going on here?
Enter the parameter NLS_DATE_LANGUAGE.
From the Database Globalization Support Guide:
“NLS_DATE_LANGUAGE
also determines the language used for:
- Month and day abbreviations returned by the
TO_CHAR
andTO_DATE
functions - Month and day abbreviations used by the default date format (
NLS_DATE_FORMAT
) - Abbreviations for AM, PM, AD, and BC” (emphasis added)
The abbreviation that you can use in to_date for AD/BC is dependent on the parameter NLS_DATE_LANGUAGE. Accidentally my setting was DUTCH. Let’s change it to AMERICAN:
SQL> alter session set nls_date_language=AMERICAN; Session altered. SQL> SQL> insert all into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC')) 2 into ot_dt values (to_date('1-jan-2017 AD','dd-mon-yyyy BC')) 3 select * from dual; 2 rows created. SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt; TO_CHAR(DUMMY,'DD-MON-YYY ------------------------- 01-jan-4712 B.C. 01-jan-2017 A.D. SQL> alter session set nls_date_language=DUTCH; Session altered. SQL> select to_char(dummy,'dd-mon-yyyy BC') from ot_dt; TO_CHAR(DUMMY,'DD-MON-YYYYB --------------------------- 01-jan-4712 V.CHR. 01-jan-2017 N.CHR. SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt; TO_CHAR(DUMMY,'DD-MON-YYYYB --------------------------- 01-jan-4712 V.CHR. 01-jan-2017 N.CHR. SQL> select to_char(dummy,'dd-mon-yyyy bc') from ot_dt; TO_CHAR(DUMMY,'DD-MON-YYYYB --------------------------- 01-jan-4712 v.Chr. 01-jan-2017 n.Chr.
The values for Dutch AD/BC are V.CHR/N.CHR or in lowercaps v.Chr. and n.Chr.
And that is also what you can use in your INSERT:
SQL> insert into ot_dt values (to_date('1-jan-4712 v.Chr.','dd-mon-yyyy bc')); 1 row created.
Good to know, #LearnSomethingNewEveryDay