select vsize(sysdate) from dual;
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: select vsize(sysdate) from dual;

  1. #1
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    There was a disccussion here about the following:

    SQL> select vsize(sysdate) from dual;

    VSIZE(SYSDATE)
    --------------
    8

    SQL> select vsize(HIRE_DATE) from EMP where rownum=1;

    VSIZE(HIRE_DATE)
    --------------------
    7

    Why Oracle needs 8 bytes (1 more than usual) for sysdate?
    Here is what I know now. DATE values are always stored in 7 bytes, excluding the length byte, within a datafile.

    Let's first dump the dates:

    SQL> SELECT dump(sysdate) FROM dual;

    DUMP(SYSDATE)
    ------------------------------------------
    Typ=13 Len=8: 209,7,10,19,9,44,19,0

    SQL> SELECT dump(HIRE_DATE) FROM EMP WHERE ROWNUM = 1;

    DUMP(HIRE_DATE)
    --------------------------------------------------
    Typ=12 Len=7: 120,101,10,17,1,1,1

    Notice the type for sysdate. It is 13, not 12. 13 is the external DATE datatype. External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.



  2. #2
    Join Date
    Jan 2000
    Posts
    387
    Interesting! Thanks for sharing!

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