This post will show how the oracle date is working and how it is not working when working with dates around year zero. This post will also show that the year zero exists in oracle.
Pls. alter date format to handle dates BC:
SQL> alter session set NLS_DATE_FORMAT='dd mon syyyy';
SQL>
The Oracle date is based on the julian date so each day has a day number:
SQL> select to_char(sysdate,'j') from dual;
2455178
And the date is based on 1. januar -4712:
SQL> select to_date(1,'j') from dual;
01 jan -4712
SQL>
So does year zero exist in oracle?
no – not in sql:
SQL> select
2 to_char(to_date('01-jan-0000','dd-mon-yyyy'),'dd mon yyyy')
3 from dual;
to_char(to_date('01-jan-0000','dd-mon-yyyy'),'dd mon yyyy')
*
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
and not in plsql:
SQL> begin
2 dbms_output.put_line( to_char(to_date('01-jan-0000','dd-mon-yyyy'),'dd mon yyyy'));
3 end;
4 /
dbms_output.put_line( to_char(to_date('01-jan-0000','dd-mon-yyyy'),'dd mon yyyy'));
*
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at line 2
What about the day before 01-jan-0001, is that working in plsql and sql:
SQL> begin
2 dbms_output.put_line(to_char(to_date('01 jan 0001','dd mon yyyy')-1,'dd mon yyyy'));
3 end;
4 /
31 dec 0000
SQL> select to_char(to_date('01 jan 0001','dd mon yyyy')-1,'dd mon yyyy') from dual;
00 000 0000
SQL>
Or – thats funny.
But what if we use the default date format:
SQL> select to_date('01 jan 0001','dd mon yyyy')-1 from dual;
31-dec-0000
SQL>
SQL> begin
2 dbms_output.put_line(to_date('01 jan 0001','dd mon yyyy')-1);
3 end;
4 /
31-dec-0000
SQL>
Conlusion about year zero dates:
- You can not enter values in string with the year zero in either sql or plsql.
- You can not use the to_char function to show year zero dates in sql, but it is ok in plsql.
The julian date is just a sequences of dates…
But because of some issues with dates, that Pope Gregory XIII fixed in the year 1582, there is some dates not existing in the Gregorian calendar calender:
SQL> declare
2 p_date date := to_date('01 oct 1582','dd mon syyyy');
3 begin
4 for i in 0..9 loop
5 dbms_output.put_line( i || ' ' || (p_date + i) || ' ' || to_char(p_date + i,'j'));
6 end loop;
7 end;
8 /
0 01-oct-1582 2299157
1 02-oct-1582 2299158
2 03-oct-1582 2299159
3 04-oct-1582 2299160
4 15-oct-1582 2299161
5 16-oct-1582 2299162
6 17-oct-1582 2299163
7 18-oct-1582 2299164
8 19-oct-1582 2299165
9 20-oct-1582 2299166
SQL>
You will note that the 5.-14. of october is missing and the julian dates are in sequence!!!
The pope also made some ajustments to the leap year. The first rule of leap year is that every 4th year an extra day will occur in february (the 24th) and therefore the date 29th february will exists. The second rule says that if it is a centery year, this will also be a leap year. But Pope Gregory XIII made an adjustment, this lead centery year will only occur every 400 year, but only in the future (after 1600). This script will show all the century leap years from year zero:
SQL> declare
2 date_1 date := to_date('31 dec -0001','dd mon syyyy');
3 last_y number := 0;
4 begin
5 for i in 1..3506390 loop
6 if to_char(date_1 + i,'yy-mm-dd') = '00-02-29' then
7 dbms_output.put_line( to_char(date_1 + i,'dd mon syyyy') || ' ' || (to_number(to_char(dat
e_1 + i,'syyyy')) -last_y) || ' ' || i);
8 last_y := to_number(to_char(date_1 + i,'syyyy'));
9 end if;
10 end loop;
11 end;
12 /
29 feb 0000 0 60
29 feb 0100 100 36585
29 feb 0200 100 73110
29 feb 0300 100 109635
29 feb 0400 100 146160
29 feb 0500 100 182685
29 feb 0600 100 219210
29 feb 0700 100 255735
29 feb 0800 100 292260
29 feb 0900 100 328785
29 feb 1000 100 365310
29 feb 1100 100 401835
29 feb 1200 100 438360
29 feb 1300 100 474885
29 feb 1400 100 511410
29 feb 1500 100 547935
29 feb 1600 100 584450
29 feb 2000 400 730547
29 feb 2400 400 876644
29 feb 2800 400 1022741
29 feb 3200 400 1168838
29 feb 3600 400 1314935
29 feb 4000 400 1461032
29 feb 4400 400 1607129
29 feb 4800 400 1753226
29 feb 5200 400 1899323
29 feb 5600 400 2045420
29 feb 6000 400 2191517
29 feb 6400 400 2337614
29 feb 6800 400 2483711
29 feb 7200 400 2629808
29 feb 7600 400 2775905
29 feb 8000 400 2922002
29 feb 8400 400 3068099
29 feb 8800 400 3214196
29 feb 9200 400 3360293
29 feb 9600 400 3506390
SQL>
You will note that there is 100 years between century leap years until year 1600, after that it will only occur every 400 years.
