Re: Which Date standard does Oracle use?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 16 Sep 1999 07:34:35 -0400
Message-ID: <1tTgN6+uQxK6ddSRO4jdLLTYBJ28@4ax.com>

A copy of this was sent to Libby Elliott <elliottel_at_my-deja.com> (if that email address didn't require changing) On Wed, 15 Sep 1999 22:02:56 GMT, you wrote:

>For Y2K reasons we had a customer ask us this question regarding our
>product and back end database. We write our product for 3 databases
>Oracle being one of them.
>
>1. What date standard is used, if any:
>International Org. for Standardization's CCYYMMDD format
>ANSI X3.30-1985
>ANSI X3.51-1994
>ISO 8601:1988
>
>So far I haven't found this information on Oracle TechNet or Metalink
>or in any documentaion we have. I did find some good info in Oracle's
>Y2K white paper, but wanted to see what info others had.

As of Oracle 8.1.5 and before, the Oracle date is stored as:

<quote>

A date in binary format contains seven bytes, as shown in Table 3–4.

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
(for 30-NOV-1992,

3:17 PM)                119  192    11  30   16     18      1

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

When a DATE column is converted to a character string in your program, it is returned using the default format mask for your session, or as specified in the INIT.ORA file.
</quote>

So, the answer is none of the above I guess.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 16 1999 - 06:34:35 CDT