[bug?] weird things with date'0000-00-00'

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[bug?] weird things with date'0000-00-00'

xenon325
'0000-00-00' date is shown as '0001-01-01'

Test case:

-----------------------------------------------------

create table anm_tmp ( id integer, d DATE );

begin
   delete anm_tmp;

   insert into anm_tmp values( 1, date'0001-01-01');
   insert into anm_tmp values( 2, date'0001-01-01' - 1 );
   insert into anm_tmp values( 3, date'0001-01-01' - 366 );
   insert into anm_tmp values( 4, date'0001-01-01' - 367 );
   insert into anm_tmp values( 5, date'0001-01-01' - 368 );
   insert into anm_tmp values( 6, TO_DATE( 1, 'J') );
   insert into anm_tmp values( 7, TO_DATE( 1, 'J') + 1721423 );
   insert into anm_tmp values( 8, TO_DATE( 1, 'J') + 1721422 );
   insert into anm_tmp values( 9, TO_DATE( 1, 'J') + 1721421 );
   
   commit;
end;

SELECT t.*, to_char(t.d, 'YYYY.MM.DD AD HH24:MI:SS'), to_char(t.d, 'J')
  FROM anm_tmp t
 ORDER BY 1  
;

-----------------------------------------------------

TOAD output:
id      d                to_char(d, 'YYYY.MM.DD...    to_char(d, 'J')
1 01.01.0001 0001.01.01 AD 00:00:00        1721424
2 01.01.0001 0000.00.00 0000000 00:00:00 0000000
3 01.01.0001 0000.00.00 0000000 00:00:00 0000000
4 31.12.0001 0001.12.31 BC 00:00:00        1721057
5 30.12.0001 0001.12.30 BC 00:00:00        1721056
6 01.01.4712 4712.01.01 BC 00:00:00        0000001
7 01.01.0001 0001.01.01 AD 00:00:00        1721424
8 01.01.0001 0000.00.00 0000000 00:00:00 0000000
9 01.01.0001 0000.00.00 0000000 00:00:00 0000000


SQL*Plus output:

        ID D        TO_CHAR(T.D,'YYYY.MM.DDADHH TO_CHAR
---------- -------- --------------------------- -------
         1 01.01.01 0001.01.01 Í.Ç. 00:00:00    1721424
         2 31.12.00 0000.00.00 0000000 00:00:00 0000000
         3 01.01.00 0000.00.00 0000000 00:00:00 0000000
         4 31.12.01 0001.12.31 ÄÎ Í.Ç. 00:00:00 1721057
         5 30.12.01 0001.12.30 ÄÎ Í.Ç. 00:00:00 1721056
         6 01.01.12 4712.01.01 ÄÎ Í.Ç. 00:00:00 0000001
         7 01.01.01 0001.01.01 Í.Ç. 00:00:00    1721424
         8 31.12.00 0000.00.00 0000000 00:00:00 0000000
         9 01.04.03 4703.04.01 ÄÎ Í.Ç. 00:00:00 0003378