Days That Don’t Exist in Oracle: In 1582…

In today’s post we want to share a curiosity that we have found working with date-type fields in Oracle. There are several days that do not exist in the database!!!!

This is due to the calendar change that occurred in 1582. Until then, from 46 BC, the Julian calendar, established by Julius Caesar, was used. In that year the calendar we currently use was established, the Gregorian calendar, promoted by Pope Gregory XIII. In that transition 10 days were lost, from October 5, 1582 to October 14, 1582.

We can check it in Oracle as follows:

SQL> select to_date(’12/10/1582′,’dd/mm/yyyy’) from dual;

TO_DATE(

——–

15/10/82

The Oracle database ignores these days that do not exist and takes as a real day the first business day after them, October 15, 1582, but does not return an error when referring to them. On the other hand, if you search for a day “that does exist”, it is displayed correctly.

SQL> select to_date(’01/10/1582′,’dd/mm/yyyy’) from dual;

TO_DATE(

——–

01/10/82

The days that do not exist can be checked with a simple program in PL / SQL, comparing with the days in the Julian calendar, which do exist.

SQL> set serveroutput on

SQL> DECLARE

2 fecha_inicio DATE := to_date(’01/10/1582′,’dd/mm/yyyy’);

3 fecha_fin DATE := to_date(’20/10/1582′,’dd/mm/yyyy’);

4 fecha DATE;

5 BEGIN

6 dbms_output.put_line(‘Fecha inicio: ‘ || to_char(fecha_inicio,’dd/mm/yyyy’));

7 dbms_output.put_line(‘Fecha inicio Juliano: ‘ || to_char(fecha_inicio,’j’));

8 dbms_output.put_line(‘Fecha fin: ‘ || to_char(fecha_fin,’dd/mm/yyyy’));

9 dbms_output.put_line(‘Fecha fin Juliano: ‘ || to_char(fecha_fin,’j’));

10 FOR i IN to_char(fecha_inicio,’j’)..to_char(fecha_fin,’j’) LOOP

11 select to_date(i,’j’) Into fecha from dual;

12 dbms_output.put_line(to_char(fecha,’dd/mm/yyyy’) || ‘ ‘ || to_char(fecha,’j’));

13 END LOOP;

14 END;

15 /

Fecha inicio: 01/10/1582

Fecha inicio Juliano: 2299157

Fecha fin: 20/10/1582

Fecha fin Juliano: 2299166

01/10/1582 2299157

02/10/1582 2299158

03/10/1582 2299159

04/10/1582 2299160

15/10/1582 2299161

16/10/1582 2299162

17/10/1582 2299163

18/10/1582 2299164

19/10/1582 2299165

20/10/1582 2299166

Procedimiento PL/SQL terminado correctamente.

You can check what happens when you try to use one of the dates that do not exist, for example in an INSERT operation.

SQL> CREATE TABLE prueba_fechas (fecha DATE);

Tabla creada.

SQL> INSERT INTO prueba_fechas VALUES (to_date(’12/10/1582′,’dd/mm/yyyy’));

1 fila creada.

SQL> SELECT * FROM prueba_fechas;

FECHA

——–

15/10/82

We hope that this post has been useful to you.

See you in future posts,

Database Team

Fonts:

CCC 11g Release 1 (11.1) (https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1838)

Calendario gregoriano. Wikipedia, La enciclopedia libre. Desde https://es.wikipedia.org/wiki/Calendario_gregoriano

Calendario juliano. Wikipedia, La enciclopedia libre. Desde https://es.wikipedia.org/wiki/Calendario_juliano

Comments are closed.