Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is Oracle8 Internal date storage format

Re: What is Oracle8 Internal date storage format

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 22 Jun 1998 20:45:59 GMT
Message-ID: <3599c213.30952607@192.86.155.100>


A copy of this was sent to danmorales_at_my-dejanews.com (if that email address didn't require changing) On Mon, 22 Jun 1998 19:57:57 GMT, you wrote:

>Question: What is the internal date storage format for Oracle 8.x ?
>
>Situation:
>----------
>The internal date storage format for Oracle 7x was seven (7) bytes.
>The seven bytes were:
>
> byte1 century (excess 100 notation)
> byte2 year (excess 100 notation)
> byte3 month
> byte4 day
> byte5 hour (excess 1 notation)
> byte6 minute (excess 1 notation)
> byte7 second (excess 1 notation)
>
>However, in Oracle 8.03 and 8.04, this seems to have changed.
>If I go through SQL to select a date into a date field, I can access
>the date correctly. However, if I want to parse the raw data myself
>(Example: say I extract row data that changed from a replication queue),
>the data is no longer like it was in Oracle 7.x as described above.
>

It has not changed, from the OCI docs for O8

DATE
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.

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).

Table 3-4 Format of the DATE Datatype
Byte 1 2 3 4 5 6 7 Meaning Century Year Month Day Hour Minute Second Example 119 192 11 30 16 18 1 (for 30-NOV-1992, 3:17 PM)

It would take forever to upgrade databases if we changed it -- its the same format in O8 as in O7 (and before)

>Please email me directly at: dmorales_at_prismsolutions.com
>
>Thanks,
>Dan Morales
>Prism Solutions, Inc.
>dmorales_at_prismsolutions.com
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Mon Jun 22 1998 - 15:45:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US