Re: ORACLE-Internal-Date-Format

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/12/22
Message-ID: <367f05c8.1368257@192.86.155.100>#1/1

A copy of this was sent to Reinhard Wahl <wahl_at_zv.fhg.de> (if that email address didn't require changing) On Mon, 21 Dec 1998 19:12:28 +0100, you wrote:

>Does anyone know how ORACLE stores Date-entries internal?
>Where can I find it in the documentation?
>

The server concepts manual talks about it in the datatype section. the oci programmers manual goes into more depth. In short, it is a 7 byte field that stores the century, year in the century, the month, the day, the hour, the minute and seconds.

More detail from the oci manual:

<quote>

The DATE datatype can update, insert, or retrieve a date value using the Oracle internal date binary format. A date in binary format contains seven bytes, as shown in Table 3 – 4.

BYTE 1 2 3 4 5 6 7 Meaning Century Year Month Day Hour Minute Second Example 119 192 11 30 16 18 1

(example bytes are for 30–NOV–1992,3:17 PM)

Table 3 – 4 Format of the DATE Datatype

The century and year bytes are in an excess–100 notation. Dates Before Common Era (BCE) are less than 100. The era begins on 01–JAN–4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess–1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).

</quote>

You can also use DUMP to inspect these values, for example:

SQL> insert into t values

   > ( to_date( '03-nov-1992 3:17:01pm', 'dd-mon-yyyy hh:mi:sspm' ) );

1 row created.

SQL> select dump(x) from t;

DUMP(X)

---

Typ=12 Len=7: 119,192,11,3,16,18,2

>Any idea?
>
>Thanks in advance
>
>Reinhard
>
>
>----------------------------------------------------
>E-Mail_at_work: mailto:wahl_at_zv.fhg.de
>E-Mail_at_home: mailto:ReinhardWahl_at_online.de
>----------------------------------------------------
>Website http://www.online.de/homepages/ReinhardWahl
>----------------------------------------------------
> Hiermit widerspreche ich der Nutzung oder
> Uebermittlung meiner Daten fuer Werbezwecke
> oder fuer die Markt- oder Meinungsforschung
> gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz.
>----------------------------------------------------
>Please do not use my E-Mail address for advertising!
>----------------------------------------------------
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Tue Dec 22 1998 - 00:00:00 CST