You may wonder why between these dates only one day:

SQL> select date'1582-10-15'-date'1582-10-04' from dual; DATE'1582-10-15'-DATE'1582-10-04' --------------------------------- 1 SQL> select date'1582-10-05' "dt_1" 2 ,date'1582-10-05' + 1 "dt_1 + 1" 3 ,date'1582-10-05' - 1 "dt_1 - 1" 4 ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date" 5 from dual; dt_1 dt_1 + 1 dt_1 - 1 dt_1 and to_date ------------------ ------------------ ------------------ ------------------ October 05, 1582 October 16, 1582 October 04, 1582 October 15, 1582

Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc

BTW, yet another trick:

SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual; DATE'0000-02-29' 'TO_CHAR:'||DATE'0000-02-29' ------------------- --------------------------- 29.02.0000 00:00:00 to_char:00.00.0000 00:00:00 SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual; select to_date('0000-02-29','yyyy-mm-dd') error from dual * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0