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 and TO_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