Re: Date Serial

A copy of this was sent to "ricky maicle" <ricky.m_at_staff.pacific.net.ph> (if that email address didn't require changing) On Fri, 17 Sep 1999 18:30:18 +0800, you wrote:

>Hello All,
>
>Date is stored internally as a double precision number.
>What function is used to get the double precision number which is equivalent
>of the date?
>
>ricky.m
>

Not so.

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

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>

It is a 7 byte field. If you are using OCI or Pro*C you can get it in its RAW format (but this is *not* encouraged). You should use to_char() on the way out of the database and to_date() on the way back in.

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