Year zero

According to Wikipedia, the year zero does not exist:

B.C. (or BC) — Before Christ. Used for years prior to AD 1, counting backwards so the year n BC is the year 1-n AD. Using these two calendar eras as historians use them means that there is no year 0 or negative year numbers.

But according to Oracle it does:

rwijk@ORA11GR1> select date '0000-01-01' from dual
  2  /

DATE'0000-01-01'
-------------------
01-01-0000 00:00:00

1 row selected.

rwijk@ORA11GR1> select date '0000-12-31' from dual
  2  /

DATE'0000-12-31'
-------------------
31-12-0000 00:00:00

1 row selected.

But wait, it really doesn't:

rwijk@ORA11GR1> select to_date('0000-01-01','yyyy-mm-dd') from dual
  2  /
select to_date('0000-01-01','yyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


rwijk@ORA11GR1> select to_date('0000-12-31','yyyy-mm-dd') from dual
  2  /
select to_date('0000-12-31','yyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Or, it does:

rwijk@ORA11GR1> select to_date('31-12-0001bc','dd-mm-yyyybc') + 1 from dual
  2  /

TO_DATE('31-12-0001
-------------------
01-01-0000 00:00:00

1 row selected.

rwijk@ORA11GR1> select to_date('01-01-0001ad','dd-mm-yyyybc') - 1 from dual
  2  /

TO_DATE('01-01-0001
-------------------
31-12-0000 00:00:00

1 row selected.

And apparently the year zero is a leap year:

rwijk@ORA11GR1> select to_date('01-01-0001 ad','dd-mm-yyyy bc')
  2         - to_date('31-12-0001 bc','dd-mm-yyyy bc')
  3    from dual
  4  /

TO_DATE('01-01-0001AD','DD-MM-YYYYBC')-TO_DATE('31-12-0001BC','DD-MM-YYYYBC')
-----------------------------------------------------------------------------
                                                                          367

1 row selected.

Confusing?
Let's inspect the internal representation:

rwijk@ORA11GR1> select dump(date '1000-01-01')
  2       , dump(date '0100-01-01')
  3       , dump(date '0010-01-01')
  4       , dump(date '0001-01-01')
  5    from dual
  6  /

DUMP(DATE'1000-01-01')          DUMP(DATE'0100-01-01')
------------------------------- -------------------------------
DUMP(DATE'0010-01-01')         DUMP(DATE'0001-01-01')
------------------------------ -----------------------------
Typ=13 Len=8: 232,3,1,1,0,0,0,0 Typ=13 Len=8: 100,0,1,1,0,0,0,0
Typ=13 Len=8: 10,0,1,1,0,0,0,0 Typ=13 Len=8: 1,0,1,1,0,0,0,0


1 row selected.

Note that 3*256 + 232 = 1000.

And now the same for the BC years:

rwijk@ORA11GR1> select dump(date '-1000-01-01')
  2       , dump(date '-0100-01-01')
  3       , dump(date '-0010-01-01')
  4       , dump(date '-0001-01-01')
  5    from dual
  6  /

DUMP(DATE'-1000-01-01')          DUMP(DATE'-0100-01-01')
-------------------------------- ---------------------------------
DUMP(DATE'-0010-01-01')           DUMP(DATE'-0001-01-01')
--------------------------------- ---------------------------------
Typ=13 Len=8: 24,252,1,1,0,0,0,0 Typ=13 Len=8: 156,255,1,1,0,0,0,0
Typ=13 Len=8: 246,255,1,1,0,0,0,0 Typ=13 Len=8: 255,255,1,1,0,0,0,0


1 row selected.

A similar formula is used to calculate the year. Only backwards starting by 255,255 (1 BC).

And the year zero apparently has its own representation:

rwijk@ORA11GR1> select dump(date '0000-01-01')
  2    from dual
  3  /

DUMP(DATE'0000-01-01')
-----------------------------
Typ=13 Len=8: 0,0,1,1,0,0,0,0

1 row selected.

Now let's see how the to_char function deals with these dates:

rwijk@ORA11GR1> select to_char(date '0001-01-01', 'dd-mm-yyyy BC')
  2       , to_char(date '0000-01-01', 'dd-mm-yyyy BC')
  3       , to_char(date '-0001-01-01', 'dd-mm-yyyy BC')
  4    from dual
  5  /

TO_CHAR(DATE' TO_CHAR(DATE' TO_CHAR(DATE'
------------- ------------- -------------
01-01-0001 AD 00-00-0000 00 01-01-0001 BC

1 row selected.

The day and month and BC indicator are '00'.

My conclusion is that internally the year zero exists in Oracle. However, the to_date and to_char functions mask this "flaw" by raising errors or printing all zeros.

Luckily, as often, the problem isn't new. A quote from this enlightening document:

After trying many date-arithmetic calculations for dates before 1 AD, we're able to state this with confidence: Oracle is using BC/AD notation but is using the astronomical convention. Presumably some Oracle programmer in the distant past lifted an astronomer's calculation algorithm and plugged it into the Oracle engine, without realizing that the negative year numbers are all exactly one year different from BC numbers. Oracle can't fix this without wrecking existing applications, although it could help by admitting there's a year zero and allowing dates in "0 AD" rather than returning an error.

Thanks Wilco and Klaas for letting me know this interesting problem.