*“Oh, what a tangled web we weave, …” *

*Sir Walter Scott*

Julian date numbers prior to AD 1 can be, well, a mess in Oracle. Take, for example, the stated date limit imposed by the database, which is January 1, 4712 BC. According to Oracle this is the start date for the Julian calendar … but it isn’t, as the start date is actually one year earlier.

Huh?!?!?

It’s true, and to explain this we need to delve into several calendars and conventions to see exactly how this came to be. We need to define a term, first, and that term is proleptic. This means to extend a calendar past the date of its inception to extrapolate dates (dates that didn’t exist in that calendar because it didn’t exist yet or it wasn’t stable). The year the Julian calendar became reliable is 4 AD, which is when the leap years stabilized [old Julius Caesar may have been a good ruler, but his math skills were apparently lacking]. Any ‘Julian’ date prior to 4 AD is considered a proleptic Julian date. And more on that later.

Julian Date Numbers (or Julian dates, as they are commonly considered) are based upon this proleptic calendar as they start at an epoch defined as January 1, 4713 BC, which is Julian Day Number 0 (since 4713 BC is WAY earlier than 4 AD [our stable starting point for the Julian calendar] this is in the proleptic Julian calendar and is, in fact, the considered ‘starting’ date for that calendar chosen because all three cycles of the Julian calendar [indiction {15-year cycle}, Metonic {roughly, a lunar cycle} and Solar] coincided for the first time). And that’s just fine, unless you run afoul of a couple of ‘gotchas‘. Those ‘gotchas’ are:

The Year Zero Rule

With the common BC/AD convention there is no Year 0, as 1 BC directly precedes 1 AD. Not so with an astronomical numbering convention, which designates years with a +/- scheme and includes a year 0 [this convention was first used by French astronomer Jacques Cassini in the mid-18th century]; the ‘AD’ years remain as-is, but the ‘BC’ end of the scale changes thusly:

2 BC is notated as -1

1 BC is notated as 0

So NOW there IS a year 0, but, well, Oracle doesn’t quite know what to do with it. And this is not the only complication, as

The inception of the Gregorian calendar

resulted in a loss of at least 10 days to correct for an excess of accumulated days due to the original, erroneous leap year rule [and we can’t thank Pope Gregory XIII enough for that]. Which 10+ days depends upon the date of adoption:

If the adoption date was October 15, 1582

—————-> then October 5 through 14 vanish.

If the adoption date was September 14, 1752

—————-> then September 3 through 13 disappear.

And there’s an option of no cutover/adoption date at all, in which the dates are considered either all Julian (going back to our proleptic Julian calendar) or all Gregorian (which establishes a proleptic Gregorian calendar) but preserves the ‘missing’ dates for eternity. [There are also other adoption dates for the Gregorian calendar throughout Europe; the two listed are the most ‘popular’.] Oracle chose to use October, 1582 as its cutover date. UNIX, on the other hand, uses the September, 1752 cutover.

So, why is Oracle unable to handle the year 0? It appears that somewhere in the development cycle a programmer decided to use the astronomical convention to calculate the Julian Day Numbers but use BC/AD notation without a correct ‘translation’. Thus the astronomical year -4712 (4712 years before year 0) becomes, in Oracle, 4712 BC instead of its actual proleptic Julian year of 4713 BC. So notation of all of the BC years in Oracle is off by 1, due to the Year 0 issue. The calculations, though, are correct, for any date on or after March 1, 4713 BC (-4712/03/01 using the astronomical notation). Why March 1, 4713 BC? Oracle considers January 1, 4713 BC as Julian Day Number 1 but it’s really Julian Day Number 0 (as mentioned previously). But, the calculation of the Julian Day Number for that date returns … 1:

```
SQL> with jdna as (
2 select floor((14 - 1)/12) a
3 from dual
4 ),
5 jdny as (
6 select -4712 + 4800 - a y
7 from dual, jdna
8 ),
9 jdnm as (
10 select 1 + (12*a) - 3 m
11 from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;
JULIAN
----------
1.15
SQL>
```

[Maybe Julian Day 0 occurs at **exactly** noon on January 1, 4713 BC, meaning 12:01 PM on that same day would start Julian Day 1. Or not.] And, since Year 4713 BC would be a leap year (don’t ask me why, it just is) it would have a February 29:

```
SQL> with jdna as (
2 select floor((14 - 2)/12) a
3 from dual
4 ),
5 jdny as (
6 select -4712 + 4800 - a y
7 from dual, jdna
8 ),
9 jdnm as (
10 select 2 + (12*a) - 3 m
11 from dual, jdna
12 )
13 select 29 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;
JULIAN
----------
59.75
SQL>
```

but Oracle doesn’t know that date exists, so the errors cancel each other by making March 1, 4713 BC Julian Day Number 60, which is correct. [Apparently Oracle is rounding the results because if we use the formula to calculate the Julian Day Number for March 1, 4713 BC we get:

```
SQL> with jdna as (
2 select floor((14 - 3)/12) a
3 from dual
4 ),
5 jdny as (
6 select -4712 + 4800 - a y
7 from dual, jdna
8 ),
9 jdnm as (
10 select 3 + (12*a) - 3 m
11 from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;
JULIAN
----------
60.4
SQL>
```

Round that calculation and you get 60; round the calculation for February 29, 4713 BC and you get … 60. This may be why Oracle refuses to accept

-4712/02/29 as a valid date:

```
SQL> select to_date('-4712/02/29','syyyy/mm/dd') from dual;
select to_date('-4712/02/29','syyyy/mm/dd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL>
```

(two dates cannot possess the same Julian Day Number as they are unique).] Any Julian Day Number from March 1, 4713 BC onward is correct. To prove this let’s generate the rounded, truncated and actual Julian day numbers for the date range of 01/01/4713 BC to 03/01/4713 BC and see what ‘falls out’:

```
SQL> with jd as (
2 SELECT to_number(to_char(daterange, 'MM')) Mth,
3 to_number(to_char(daterange, 'DD')) D,
4 to_number(to_char(daterange, 'SYYYY')) YR
5 FROM
6 (
7 select to_date('01 -4712','MM SYYYY') + (level - 1) as DateRange
8 from dual
9 where (to_date('01 -4712','MM SYYYY')+level-1) <= last_day(sysdate)
10 connect by level<=60
11 )
12 union
13 select 2, 29, -4712 from dual
14 ORDER BY 1,2,3
15 ),
16 jdna as (
17 select floor((14 - mth)/12) a, mth mh, d dy, yr yy
18 from jd
19 ),
20 jdny as (
21 select yy + 4800 - a y, mh mn, dy dn, yy yn
22 from jdna
23 ),
24 jdnm as (
25 select mh + (12*a) - 3 m, mh mm, dy dm, yy ym
26 from jdna
27 )
28 select mh, dy,
29 case when yy< 1 then abs(1 - yy) else yy end yr,
30 case when yy < 1 then 'BC' else 'AD' end era,
31 dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian,
32 round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian,
33 trunc(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian,
34 case when mh < 3 and yy < 1 then
35 round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)-1
36 else
37 round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)
38 end actual_julian
39 from jdna, jdnm, jdny
40 where jdnm.mm = jdna.mh
41 and jdny.mn = jdna.mh
42 and jdnm.dm = jdna.dy
43* and jdny.dn = jdna.dy
MO DY YR ER JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN
---------- ---------- ---------- -- ---------- ---------- ------------ -------------
1 1 4713 BC 1.15 1 1 0
1 2 4713 BC 2.15 2 2 1
1 3 4713 BC 3.15 3 3 2
1 4 4713 BC 4.15 4 4 3
1 5 4713 BC 5.15 5 5 4
1 6 4713 BC 6.15 6 6 5
1 7 4713 BC 7.15 7 7 6
1 8 4713 BC 8.15 8 8 7
1 9 4713 BC 9.15 9 9 8
1 10 4713 BC 10.15 10 10 9
1 11 4713 BC 11.15 11 11 10
1 12 4713 BC 12.15 12 12 11
1 13 4713 BC 13.15 13 13 12
1 14 4713 BC 14.15 14 14 13
1 15 4713 BC 15.15 15 15 14
1 16 4713 BC 16.15 16 16 15
1 17 4713 BC 17.15 17 17 16
1 18 4713 BC 18.15 18 18 17
1 19 4713 BC 19.15 19 19 18
1 20 4713 BC 20.15 20 20 19
1 21 4713 BC 21.15 21 21 20
1 22 4713 BC 22.15 22 22 21
1 23 4713 BC 23.15 23 23 22
1 24 4713 BC 24.15 24 24 23
1 25 4713 BC 25.15 25 25 24
1 26 4713 BC 26.15 26 26 25
1 27 4713 BC 27.15 27 27 26
1 28 4713 BC 28.15 28 28 27
1 29 4713 BC 29.15 29 29 28
1 30 4713 BC 30.15 30 30 29
1 31 4713 BC 31.15 31 31 30
2 1 4713 BC 31.75 32 31 31
2 2 4713 BC 32.75 33 32 32
2 3 4713 BC 33.75 34 33 33
2 4 4713 BC 34.75 35 34 34
2 5 4713 BC 35.75 36 35 35
2 6 4713 BC 36.75 37 36 36
2 7 4713 BC 37.75 38 37 37
2 8 4713 BC 38.75 39 38 38
2 9 4713 BC 39.75 40 39 39
2 10 4713 BC 40.75 41 40 40
2 11 4713 BC 41.75 42 41 41
2 12 4713 BC 42.75 43 42 42
2 13 4713 BC 43.75 44 43 43
2 14 4713 BC 44.75 45 44 44
2 15 4713 BC 45.75 46 45 45
2 16 4713 BC 46.75 47 46 46
2 17 4713 BC 47.75 48 47 47
2 18 4713 BC 48.75 49 48 48
2 19 4713 BC 49.75 50 49 49
2 20 4713 BC 50.75 51 50 50
2 21 4713 BC 51.75 52 51 51
2 22 4713 BC 52.75 53 52 52
2 23 4713 BC 53.75 54 53 53
2 24 4713 BC 54.75 55 54 54
2 25 4713 BC 55.75 56 55 55
2 26 4713 BC 56.75 57 56 56
2 27 4713 BC 57.75 58 57 57
2 28 4713 BC 58.75 59 58 58
2 29 4713 BC 59.75 60 59 59
3 1 4713 BC 60.4 60 60 60
SQL>
```

Notice if the calculation is rounded then Februrary 29, 4713 BC has the same Julian Day Number as March 1, 4713 BC (as noted earlier); if the calculation is truncated then February 1, 4713 BC has the same Julian Day Number as January 31, 4713 BC and, well, we can’t have a calendar where February 1 doesn’t exist thus the reason [I suspect] that Oracle chose to round the value and then discard February 29, 4713 BC. But, had Oracle performed the numeric ‘gyrations’ illustrated in the query above we could have had proper Julian Day numbers and all of the actual proleptic dates of 4713 BC would exist.

[The above query works on Oracle releases 10.2.x and later due to the ‘connect by level<=60' statement, which produces a date list of 60 values used to generate the posted table. In releases 9.x that statement will parse and execute but may not provide any more than one row of output; in 8.x and earlier that statement will produce a syntax error.]

*(Notice that the query uses the “&&” syntax for positional variables; this allows the variable to be set once, when the script is called, and passes the set value through the script to any other places where it is used. Using a single “&” would cause SQL*Plus to request the value for each occurrence in the statement. The up side to this is that variables need only be supplied values once during a run; the down side is the values are persistent between runs and either need to be submitted again on the command line [for a script] or need an explicit ‘undefine’ statement to clear them [as in ‘undefine 1’, for example, to ‘clear’ the value for &&1]. The double & can also be used on named variables, such as &&my_tbl; the undefine syntax works on these, as well, so that ‘undefine my_tbl’ will clear any set value supplied so subsequent runs will request a new value.)*

And this gets better. Oracle may not recognize 1 BC as year 0 but the calculations do. Ask Oracle for the number of days difference between December 31, 1 BC and January 1, 1 AD:

```
SQL> select to_date('01/01/0001', 'mm/dd/yyyy') - to_date('12/31/0001 BC', 'mm/dd/yyyy bc') "This Should Be 1"
2 from dual;
This Should Be 1
----------------
367
SQL>
```

Now let’s ask Oracle to add 1 to the date December 31, 1 BC:

```
SQL> select to_date('31-DEC-0001 BC', 'DD-MON-YYYY AD')+1 from dual
2 /
TO_DATE('31
-----------
01-JAN-0000
SQL>
```

So Year 0 exists (at least according to the JDN formula and an interesting date addition illustrated above) and it’s a leap year. Obviously Oracle takes the 1 BC value and simply tacks on a minus sign, making the year -1 in the astronomical convention — which is wrong as discussed previously. Were Oracle to do this correctly 1 BC would become Year 0 and the returned value for the above query would be 1:

```
SQL> with jdna as (
2 select floor((14 - &&1)/12) a
3 from dual
4 ),
5 jdny as (
6 select &&3 + 4800 - a y
7 from dual, jdna
8 ),
9 jdnm as (
10 select &&1 + (12*a) - 3 m
11 from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14 case when &&3 with jdna as (
2 select floor((14 - &&1)/12) a
3 from dual
4 ),
5 jdny as (
6 select &&3 + 4800 - a y
7 from dual, jdna
8 ),
9 jdnm as (
10 select &&1 + (12*a) - 3 m
11 from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14 case when &&3 select &after_jc - &before_jc from dual;
1721424-1721423
---------------
1
SQL>
```

(Oracle recognizes this as a bug (106242) but, due to possible backwards compatibility issues, hasn’t fixed it.)

Can you fix the display issue in Oracle? Well, sort of — you’ll need to generate the date string, parse it, then, if it’s a BC date add one more to the year:

```
SQL> declare
2 yr number;
3 bc varchar2(2);
4 mo varchar2(10);
5 dy varchar2(2);
6
7 begin
8 select to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'AD'),
9 to_number(to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'YYYY')),
10 to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'MONTH'),
11 to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'DD')
12 into bc, yr, mo, dy
13 from dual;
14
15 if bc = 'BC' then
16 yr := yr + 1;
17 end if;
18
19 dbms_output.put_line('The date submitted is '||mo||' '||dy||', '||yr||' '||bc);
20
21 end;
22 /
The date submitted is JANUARY 01, 4713 BC
PL/SQL procedure successfully completed.
SQL>
```

It may be easier to simply remember the BC date notation is one year off.

But, hey, the Julian Date Numbers are correct … if you don’t count January and February of 4713 BC … so is Oracle wrong or is the formula wrong? It appears a bit of both, really, as the formula (as proven) returns the wrong values for dates from the epoch through February 29, 4713 BC, and Oracle doesn’t ‘translate’ the astronomical numbering convention correctly, associating -1 with 1 BC. When it rains, it pours.

Now, where did I put that leap year?

great article – good choice of date since Year 4713 would be a leap year

Comment by Jamie — April 23, 2009 @ 15:49 |

[…] you can also use Julian date format, which counts the number of days from 01-01-4712 BC to the desired date, putting these values in the 2.4 million range of […]

Pingback by How About A Date? « Oracle Tips and Tricks — July 22, 2012 @ 22:08 |

4713 BC is leap year because of the lack of the year AD 0. If AD 4 is aleap year then AD 0 = 1 BC is also a (Julian) leap year. Likewise, a Julian leap year should occur 4712 years before that.

Comment by Swin — August 29, 2014 @ 10:35 |