doubt in Oracle default format of date storing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: doubt in Oracle default format of date storing

  1. #1
    Join Date
    Apr 2003
    Posts
    29

    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

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i think,

    none of them.

    stored as seconds elapsed since sometime(1900?).

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Read the 9i Concepts Guide at http://tahiti.oracle.com , Chapter 12 "Native Datatypes".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by rajabalachandra
    stored as seconds elapsed since sometime(1900?).
    Nothing like that either.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Is it true that they are called Julian dates because SYSDATE=0 was Julian's birthday?

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

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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)
    Last edited by DaPi; 02-06-2004 at 06:53 AM.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Even Oracle 7.3.4 accepts the years between -4713 and 9999.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width