Oracle SQL

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

Oracle SQL

Tag Archives: oracle calendar

Why between to_date(’1582-10-15′,’yyyy-mm-dd’) and to_date(’1582-10-04′,’yyyy-mm-dd’) only one day