Re: Internal date format (numerical)

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 2 Dec 2005 17:12:05 +0100
Message-ID: <43907258$0$28134$626a14ce@news.free.fr>

"David Cressey" <david.cressey_at_earthlink.net> a écrit dans le message de news: 00Xjf.8121$N45.7545_at_newsread1.news.atl.earthlink.net...
|
|
| Actually, I believe that Oracle stores about half of its range as "days
| before day zero", which I'll take as "negative dates".

No, you should reread Mark (++mcs) previous post: <quote>
oracle dates don't have a 'base' figure -- their internal storage has individual components for each part of the date from year to second -- check out the docs.
</quote>

Oracle DOES NOT store date as a number.
It stores it at 1 byte for century(+100), 1 for decade/year(+100), 1 for month, 1 for day, 1 for hour(+1), 1 for minute(+1) and 1 for second(+1).

SQL> create table t (col date);

Table created.

SQL> insert into t values (sysdate);

1 row created.

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select col, dump(col) d from t;

COL                 D
------------------- ----------------------------------------

2005/12/02 17:08:29 Typ=12 Len=7: 120,105,12,2,18,9,30

1 row selected.

Regards
Michel Cadot Received on Fri Dec 02 2005 - 10:12:05 CST