Oracle – Inserting Year with Leading Zero

oracleoracle-11g

Is it possible to enter a date with a zero year?
What would the insert clause look like?

I ask, as an application is doing this, see the dump of the date:

dt_data: 05/01/00
dump(dt_data): Typ=12 Len=7: 100,100,5,1,1,1,1
to_char(dt_data, 'dd/MM/yyyy'): 00/00/0000

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Best Answer

SQL> create table t1 (c1 date);

Table created.

SQL> insert into t1 values (date'0000-01-05');

1 row created.

SQL> commit;

Commit complete.

SQL> select c1, to_char(c1, 'dd/MM/yyyy'), dump(c1) from t1;

C1                  TO_CHAR(C1
------------------- ----------
DUMP(C1)
--------------------------------------------------------------------------------
0000-01-05 00:00:00 00/00/0000
Typ=12 Len=7: 100,100,1,5,1,1,1
Related Question