1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Maximum Date Data Type Size

Discussion in 'SQL PL/SQL' started by ygsunilkumar, Mar 17, 2009.

  1. ygsunilkumar

    ygsunilkumar Active Member

    Likes Received:
    Trophy Points:
    What is the maximum data type size allowed for DATE in Oracle 9i? When Table Structure described, DATE datatype will be displayed and not datatype size. This is an Interview Question.

  2. rajavu

    rajavu Forum Guru

    Likes Received:
    Trophy Points:
    @ Bangalore , India
    A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT. As of now. t is th same case for all versions of Oracle.

    Datatype TIMESTAMP is having variable size from 7 to 11 to 13.
  3. orafan

    orafan Active Member

    Likes Received:
    Trophy Points:
    The oracle DATE datatype can range from January 1, 4712 BC to December 31, 9999 AD.
    DATE values need seven bytes for storage. The storage needs of TIMESTAMP values vary between seven and 11 bytes (variable length data type).

    For DATE, the first byte stores century, the second stores year, the third stores month, the fourth stores day, the fifth stores hour, the sixth stores minutes, and the seventh stores seconds. While for TIMESTAMP values, the first seven bytes are similar to those of the DATE value, and the last four bytes represent the fractional second's value—which is up to a precision of nanoseconds. However, if you store a DATE value in a TIMESTAMP column, it needs only seven bytes for storage, like a DATE value.