doubt in Oracle default format of date storing

can any one tell me in which format oracle stores the date in its memory.

is it DD-MON-YY or DD-MON-YYYY or DD-MON-YY HH24:MISS or DD-MON-YY HH12MISS

i think,

none of them.

stored as seconds elapsed since sometime(1900?).

Read the 9i Concepts Guide at http://tahiti.oracle.com , Chapter 12 "Native Datatypes".

Quote:

---

Originally posted by rajabalachandra
stored as seconds elapsed since sometime(1900?).

---

Nothing like that either.

Is it true that they are called Julian dates because SYSDATE=0 was Julian's birthday? :D

(BTW: type 60 into an Excel spread-sheet and format the cell as a date - Bill Gates strikes again.)

are both methods used?

Oracle uses its own internal format to store dates.
Date data is stored in fixed-length fields of seven bytes each,
corresponding to century, year, month, day, hour, minute, and second.

http://download-west.oracle.com/docs...4dat.htm#41259


Julian dates allow continuous dating by the number of days
from a common reference. (The reference is 01-01-4712 years
BCE, so current dates are somewhere in the 2.4 million range.)
A Julian date is nominally a noninteger, the fractional part
being a portion of a day. Oracle uses a simplified approach
that results in integer values. Julian dates can be calculated
and interpreted differently. The calculation method used by
Oracle results in a seven-digit number (for dates most often
used), such as 2449086 for 08-APR-93.

http://download-west.oracle.com/docs...3datyp.htm#796

It's a bit confusing: although the dates are stored in that special 7-byte format, date arithmetic behaves as if the date/time combination is number (Julian date including fractions of day) - so you can:
Select sysdate - trunc(sysdate) from dual; --gives the time as a fraction of a day since midnight.
Select sysdate + 1 from dual; --tells you what tommorow is.

(BTW: since version 8 or 8i, the upper limit is 31-DEC-9999 AD)

Even Oracle 7.3.4 accepts the years between -4713 and 9999.

Tamil