select vsize(sysdate) from dual;

[julian]

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.

[mooks]

Interesting! Thanks for sharing!